Passing result sets: patindex with nvarchar

Hi Microsoft,

My Name is Harshal Choksi. i am working in microsoft technolgy in .NET and SQL Server 2000. we are storing some unicode data in database in table which has a nvarchar data types. i have written one function in SQL which contains some T-Transact function like SubString, Len & PatIndex. in which i m not getting any value with PatIndex, it shows me always column with 0 value.

Means my patindex is not working well if i have 'hindi text'.

GO
SELECT PATINDEX ('%??????%', keyword)

FROM tblKeywords where keywordID = 68
GO

where i tblKeyword is a tableName and keyword is a column name. "??????" is text what my parameter in function .keyword is column name which has a datatype NVARCHAR. i m getting result with in result wizard 0 each time, instead of "??????", if i write something "London",then it will show me right value.

so please help in this manner. i would be feel great if you would me help so. i have to implement some search functionality with hindi word..... i could use Contains keyword in SQL in wheere condion but i want to use patIndex only. so help me as soon as possible....

I will be waiting for your reply.

Thanks in advance.

You have to put N prefix on Nvarchar String values

See the sample here..

Code Snippet

SELECT PATINDEX ('%??????%', N'??????????????')

--Output : 0

SELECT PATINDEX (N'%??????%', N'??????????????')

--Output : 9

--InYour Query

SELECT

PATINDEX (N'%??????%', keyword)

FROM

tblKeywords where keywordID = 68

|||

Hi Manivannan, Thanks for your help.. yet still not getting output. i have written function is SQL as below,u might have some sort of idea about it. & this function i m using in my stored procedure... which also i am mentioning below this SQL function.

--SQL Function

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

--
-- GetKeywordsFromQuery(query)
-- SUMMARY Parses a full-text query for keywords that can be used in a keyword (rather
-- than full-text) search
-- PARAMS query Query containing one or more keywords
-- RETURNS A temporary TABLE containing the found keywords
--
ALTER FUNCTION GetKeywordsFromQuery
(
@.query nvarchar(255)
)
RETURNS @.words TABLE (Word nvarchar(255)COLLATE DATABASE_Default)
AS
BEGIN
-- Define processing flags
-- NOTE A processing flag is used to ensure that words are excluded from
-- NOTE the results if they are preceeded with a "NOT" or that a complete
-- NOTE phrase is added as a whole (rather than seperate words)
DECLARE @.isNotWord bit
SET @.isNotWord = 0
DECLARE @.isPhrase bit
SET @.isPhrase = 0
-- Define word variables
DECLARE @.word AS nvarchar(255)
DECLARE @.phrase AS nvarchar(255)
DECLARE @.substring AS nvarchar(255)
SET @.substring = @.query
-- Find the first space
DECLARE @.spacePosition AS bigint
SET @.spacePosition = PATINDEX(N'% %', @.query)
-- Iterate over the query until all words have been found
WHILE 0 <= @.spacePosition
BEGIN
-- Get the next word in the query
IF(0 != @.spacePosition)
SET @.word = SUBSTRING(@.substring, 1, (@.spacePosition -1))
ELSE
BEGIN
SET @.word = @.substring
SET @.spacePosition = -1
END
-- Check for a phrase
IF(N'"' = SUBSTRING(@.word, 1, 1))
BEGIN
-- Start the phrase
SET @.isPhrase = 1
SET @.phrase = SUBSTRING(@.word, 2, LEN(@.word) - 1)
END
ELSE IF(N'"' = SUBSTRING(@.word, LEN(@.word), 1))
BEGIN
-- Complete the phrase
SET @.isPhrase = 0
SET @.phrase = @.phrase + SPACE(1) + SUBSTRING(@.word, 1, LEN(@.word) - 1)
SET @.word = @.phrase
END

ELSE IF(1 = @.isPhrase)
-- Append the current word to the phrase
SET @.phrase = @.phrase + SPACE(1) + @.word
-- Add the word to temporary table
IF (UPPER('NEAR') != UPPER(@.word))
AND (UPPER('AND') != UPPER(@.word))
AND (UPPER('NOT') != UPPER(@.word))
AND (0 = @.isNotWord)
AND (0 = @.isPhrase)
BEGIN
-- This word can be used to search for keywords
INSERT @.words VALUES(@.word)
SET @.isNotWord = 0
END
-- Reset the "Not Word" exclusion flag
-- NOTE This is for cases where an AND or NEAR follows an AND NOT
ELSE IF (UPPER('NEAR') = UPPER(@.word))
OR (UPPER('AND') = UPPER(@.word))
SET @.isNotWord = 0
-- Indicate that the next word needs to be excluded
ELSE IF (UPPER('NOT') = UPPER(@.word))
SET @.isNotWord = 1
-- Move on to the next word or exit the WHILE
SET @.substring = SUBSTRING(@.substring, (@.spacePosition + 1), LEN(@.substring) - (@.spacePosition))
IF(-1 != @.spacePosition)
SET @.spacePosition = PATINDEX(N'% %', @.substring)
END
RETURN
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Here this is my sp:

SELECT DISTINCT c.ContentName, c.ContentLongSummary, c.MasterId, nc.NavigationId
FROM tblContents AS c
INNER JOIN tblKeywords AS k ON k.KeywordId = c.KeywordId
INNER JOIN GetKeywordsFromQuery(@.query) AS kq ON kq.Word = k.Keyword
WHERE c.SiteId = @.siteId
AND c.StatusID = 2
AND c.DeletedBy IS NULL

so i have to sort out this this thing as well,can you please look at thru my function as well stored procedure.

Thanks in Advance.

|||

so please give me solution as possible, here i have written my stored procedure as below which use above function:

I am using for all column nvarchar only in which i stored hindi text and English as well.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

--
-- spSearch
-- SUMMARY Executes a search using the specified query
-- PARAMS @.siteId Identifier of the site from which the search request was generated
-- @.query Full-text query to be executed against the full-text engine
--
ALTER PROCEDURE spSearch
(
@.siteId INT,
@.textOnly BIT,
@.query NVARCHAR(4000)
)
AS
-- Select Alternatives
Declare @.partialQuery NVARCHAR(50)
Declare @.keyphrase NVARCHAR(50)
--Query can be provided like 'searchterm' or as "search term" depending in existance of space character
--must format the partial query if it contains a space like '"search term*"'
--the keyphrase is @.query without quotes
IF CHARINDEX('"',@.query) > 0
BEGIN
SET @.partialQuery = LEFT(@.query, LEN(@.query) - 1) + '*"'
SET @.keyphrase = LEFT(RIGHT(@.query, LEN(@.query) - 1), LEN(@.query) - 2)
END
ELSE
BEGIN
--must format the partial query like '"searchterm*"'
set @.partialQuery = ' "' + @.query + '*" '
set @.keyphrase = @.query
END

--Now we can search for alternatives as exact match of @.query on alternatives
--or partial match on keyword, but ignore exact match on keyword (i.e. only alternatives to @.query)
SELECT *
FROM tblKeywords
WHERE NOT Keyword = @.keyphrase
AND SiteId = @.siteId
AND (
CONTAINS(Keyword, @.partialQuery )
)

-- Select the pages that match the query
SELECT DISTINCT c.ContentName, c.ContentLongSummary, c.MasterId, nc.NavigationId
FROM tblContents AS c
INNER JOIN trelKeywords AS ck ON ck.MasterId = c.MasterId
INNER JOIN tblKeywords AS k ON k.KeywordId = ck.KeywordId
INNER JOIN trelNavigationContents AS nc ON nc.MasterId = c.MasterId
INNER JOIN GetKeywordsFromQuery(@.query) AS kq ON kq.Word = k.Keyword
INNER JOIN tblNavigation AS nav ON nc.NavigationId = nav.NavigationId
WHERE c.SiteId = @.siteId
AND c.StatusID = 2
AND c.DeletedBy IS NULL
AND ( (@.textOnly=0) or (nav.ExcludeFromTextSite=0 and @.textOnly=1) )
UNION -- the following selects list pages that match, with primarylist page as their navigationid
SELECT DISTINCT c.ContentName, c.ContentLongSummary, c.MasterId, nc.NavigationId
FROM tblContents AS c
INNER JOIN trelKeywords AS ck ON ck.MasterId = c.MasterId
INNER JOIN tblKeywords AS k ON k.KeywordId = ck.KeywordId
INNER JOIN tblContents AS c1 ON c.PrimaryListMasterId = c1.MasterId
INNER JOIN trelNavigationContents AS nc ON nc.MasterId = c1.MasterId
INNER JOIN GetKeywordsFromQuery(@.query) AS kq ON kq.Word = k.Keyword
INNER JOIN tblNavigation AS nav ON nc.NavigationId = nav.NavigationId
INNER JOIN tblListContents AS lst ON lst.ListItemMasterId = c.MasterId
WHERE c.SiteId = @.siteId
AND c.DeletedBy IS NULL
AND c.StatusID = 2
AND c1.statusid = 2
AND c1.DeletedBy IS NULL
AND ( (@.textOnly=0) or (nav.ExcludeFromTextSite=0 and @.textOnly=1) )
AND lst.ContentMasterId = c.PrimaryListMasterId

-- Select the downloads that match the query
SELECT DISTINCT r.ResourceId, r.ResourceName, r.ResourceLongSummary, r.FileSize
FROM tblResources AS r
INNER JOIN trelKeywordResources AS kr ON kr.ResourceId = r.ResourceId
INNER JOIN tblKeywords AS k ON k.KeywordId = kr.KeywordId
INNER JOIN GetKeywordsFromQuery(@.query) AS kq ON kq.Word = k.Keyword
INNER JOIN trelContentItemResources AS cir ON r.ResourceId = cir.ResourceId
WHERE r.ResourceTypeId = 2
AND r.SiteId = @.siteId
AND cir.StatusId = 2

SELECT CASE WHEN nc.MasterId IS NULL THEN cir.MasterId ELSE nc.MasterId END AS MasterId, CASE WHEN nc.MasterId IS NULL
THEN ln.NavigationId ELSE nc.NavigationId END AS NavigationId, c.ContentName, res.ResourceId, res.ResourceTextType, res.ResourceText,
c.ContentLongSummary
FROM tblResources res INNER JOIN
trelContentItemResources cir ON cir.ResourceId = res.ResourceId
LEFT OUTER JOIN trelNavigationContents nc ON nc.MasterId = cir.MasterId
LEFT OUTER JOIN tblContents c ON c.MasterId = cir.MasterId AND c.StatusId = cir.StatusId
LEFT OUTER JOIN trelNavigationContents ln ON c.PrimaryListMasterId = ln.MasterId
LEFT OUTER JOIN tblNavigation nav ON nc.NavigationId = nav.NavigationId
INNER JOIN tlkpResourceTypes rt ON rt.ResourceTypeId = res.ResourceTypeId
INNER JOIN tblListContents AS lst ON lst.ListItemMasterId = c.MasterId
WHERE (res.SiteId = @.siteId)
AND (res.ResourceTypeId = 1 OR res.ResourceTypeId = 6)
AND CONTAINS(res.ResourceText, @.query)
AND (c.ContentPublicationDate IS NOT NULL)
AND (c.DeletedBy IS NULL)
AND (c.StatusId = 2)
AND (nc.MasterId IS NOT NULL)
AND ( (@.textOnly=0) or (nav.ExcludeFromTextSite=0 and @.textOnly=1) )
OR
(res.SiteId = @.siteId)
AND (res.ResourceTypeId = 1 OR res.ResourceTypeId = 6)
AND CONTAINS(res.ResourceText, @.query)
AND (c.ContentPublicationDate IS NOT NULL)
AND (c.DeletedBy IS NULL)
AND (c.StatusId = 2)
AND (c.PrimaryListMasterId IS NOT NULL)
AND ( (@.textOnly=0) or (nav.ExcludeFromTextSite=0 and @.textOnly=1) )
ORDER BY rt.SearchResultOrder, c.ContentName

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

|||

Your query is working fine on my mac..

I tried with following statement ...

Code Snippet

Select * From dbo.GetKeywordsFromQuery(N'"SQL Server" is "very good" AND "very Powerfull" ?????? ?????? AND ?????? ????????????????????????')

OUTPUT:

Word

SQL Server

very good

very Powerfull

??????

????????????????????????

What I am doubting here is @.QUERY parameter.

Pls check the datatype. And when you call the SP you should prefix the N.

Example:

Code Snippet

Exec dbo.YourSP @.query = N'"SQL Server" is "very good" AND "very Powerfull" ?????? ?????? AND ?????? ????????????????????????')

|||

yeah thanks for your help,but can you check my storedProcedure as well?

Please look at it & if you have any idea of it.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

--
-- spSearch
-- SUMMARY Executes a search using the specified query
-- PARAMS @.siteId Identifier of the site from which the search request was generated
-- @.query Full-text query to be executed against the full-text engine
--
ALTER PROCEDURE spSearch
(
@.siteId INT,
@.textOnly BIT,
@.query NVARCHAR(4000)
)
AS
-- Select Alternatives
Declare @.partialQuery NVARCHAR(50)
Declare @.keyphrase NVARCHAR(50)
--Query can be provided like 'searchterm' or as "search term" depending in existance of space character
--must format the partial query if it contains a space like '"search term*"'
--the keyphrase is @.query without quotes
IF CHARINDEX(N'"',@.query) > 0
BEGIN
SET @.partialQuery = LEFT(@.query, LEN(@.query) - 1) + N'*"'
SET @.keyphrase = LEFT(RIGHT(@.query, LEN(@.query) - 1), LEN(@.query) - 2)
END
ELSE
BEGIN
--must format the partial query like '"searchterm*"'
set @.partialQuery = N' "' + @.query + N'*" '
set @.keyphrase = @.query
END

-- Select the pages that match the query
SELECT DISTINCT c.ContentName, c.ContentLongSummary, c.MasterId, nc.NavigationId
FROM tblContents AS c
INNER JOIN trelKeywords AS ck ON ck.MasterId = c.MasterId
INNER JOIN tblKeywords AS k ON k.KeywordId = ck.KeywordId
INNER JOIN trelNavigationContents AS nc ON nc.MasterId = c.MasterId
INNER JOIN GetKeywordsFromQuery (@.query) AS kq ON kq.Word = k.Keyword
INNER JOIN tblNavigation AS nav ON nc.NavigationId = nav.NavigationId
WHERE c.SiteId = @.siteId
AND c.StatusID = 2
AND c.DeletedBy IS NULL
AND ( (@.textOnly=0) or (nav.ExcludeFromTextSite=0 and @.textOnly=1) )
UNION -- the following selects list pages that match, with primarylist page as their navigationid
SELECT DISTINCT c.ContentName, c.ContentLongSummary, c.MasterId, nc.NavigationId
FROM tblContents AS c
INNER JOIN trelKeywords AS ck ON ck.MasterId = c.MasterId
INNER JOIN tblKeywords AS k ON k.KeywordId = ck.KeywordId
INNER JOIN tblContents AS c1 ON c.PrimaryListMasterId = c1.MasterId
INNER JOIN trelNavigationContents AS nc ON nc.MasterId = c1.MasterId
INNER JOIN GetKeywordsFromQuery (@.query) AS kq ON kq.Word = k.Keyword
INNER JOIN tblNavigation AS nav ON nc.NavigationId = nav.NavigationId
INNER JOIN tblListContents AS lst ON lst.ListItemMasterId = c.MasterId
WHERE c.SiteId = @.siteId
AND c.DeletedBy IS NULL
AND c.StatusID = 2
AND c1.statusid = 2
AND c1.DeletedBy IS NULL
AND ( (@.textOnly=0) or (nav.ExcludeFromTextSite=0 and @.textOnly=1) )
AND lst.ContentMasterId = c.PrimaryListMasterId

-- Select the downloads that match the query
SELECT DISTINCT r.ResourceId, r.ResourceName, r.ResourceLongSummary, r.FileSize
FROM tblResources AS r
INNER JOIN trelKeywordResources AS kr ON kr.ResourceId = r.ResourceId
INNER JOIN tblKeywords AS k ON k.KeywordId = kr.KeywordId
INNER JOIN GetKeywordsFromQuery (@.query) AS kq ON kq.Word = k.Keyword
INNER JOIN trelContentItemResources AS cir ON r.ResourceId = cir.ResourceId
WHERE r.ResourceTypeId = 2
AND r.SiteId = @.siteId
AND cir.StatusId = 2

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

you call the SP you should prefix the N

in this sp, i m using three times that function. even i m not getting how to call the sp with it should have prefix the N....please let me know. i am just waiting for your reply.even i have using NVARCHAR whereever i stored a hindi text...i hope you have already seen my function.please do the needful. i have tried lot since few days on this, still couldn't get the right solution.

|||

can u tell me how you are executing your sp ..

I need the Exec spSearch .... statement

|||

well i don't have written something like Exec spSearch statement, actually i m calling this function from my ASP.NET application that's all. passing a 3 parameters and wanted to get results accordingly. Waiting for your reply.

Thanks Again for your response.

|||

hey check your ASP.NET code, the param declaration. It should be NVARCHAR.

Test your SP from Query Analyser using EXEC statement.. So you can validate where the problem resides.

|||

well,even not my sp giving me results at all if i pass a parameters,i checked using Exec statement also,not getting result..in ASP.NET i have written someting:--

public virtual SafeSqlDataReader GetSearchResults(int siteId, bool textOnly, string input)

{

// Define the command

SqlCommand command = new SqlCommand();

command.CommandType = CommandType.StoredProcedure;

command.CommandText = StoredProcedures.spSearch.ToString();

// Set the parameters

command.Parameters.Add("@.siteId", siteId);

command.Parameters.Add("@.textOnly", textOnly);

command.Parameters.Add("@.query", input);

command.Connection = SqlHelperWrapper.OpenConnection(this.ConnectionString);

return new SafeSqlDataReader(command);

}

so not getting a problem where it might be? if i have declared varchar here also then problem still remain same for the Stored Procedure because not giving me result at all if i pass a parameter in stored procedure,so problem is with sp and then we solved a problem with our code.

waiting for your reply.

|||

Hi still i am waiting for your response. Let me know if any solution you have for this, i am not getting this thing, i have written in my .NET code varchar , but if i pass parameters for hindi, not getting a results at all. so stored procedure may have some problem.

Please give me any solution for this.

Waiting for your response soon.

|||

how is it work with Patindex if i have one column which have data type 'Image', i want to search some hindi text within that, i have written a function in SQL which contains some Patindex and some string function as well. i have written something like:- PATINDEX(N'% %', @.query), but i want to find a some value with the help of stored procesedure:

here is my query which is a part of my stored procedure:

SELECT DISTINCT r.ResourceId, r.ResourceName, r.ResourceLongSummary, r.FileSize
FROM tblResources AS r
INNER JOIN trelKeywordResources AS kr ON kr.ResourceId = r.ResourceId
INNER JOIN tblKeywords AS k ON k.KeywordId = kr.KeywordId
INNER JOIN GetKeywordsFromQuery(@.query) AS kq ON kq.Word = k.Keyword
INNER JOIN trelContentItemResources AS cir ON r.ResourceId = cir.ResourceId
WHERE r.ResourceTypeId = 2
AND r.SiteId = @.siteId
AND cir.StatusId = 2
AND k.SiteId = @.siteId

SELECT CASE WHEN nc.MasterId IS NULL THEN cir.MasterId ELSE nc.MasterId END AS MasterId, CASE WHEN nc.MasterId IS NULL
THEN ln.NavigationId ELSE nc.NavigationId END AS NavigationId, c.ContentName, res.ResourceId, res.ResourceTextType, res.ResourceText,
c.ContentLongSummary
FROM tblResources res INNER JOIN
trelContentItemResources cir ON cir.ResourceId = res.ResourceId
LEFT OUTER JOIN trelNavigationContents nc ON nc.MasterId = cir.MasterId
LEFT OUTER JOIN tblContents c ON c.MasterId = cir.MasterId AND c.StatusId = cir.StatusId
LEFT OUTER JOIN trelNavigationContents ln ON c.PrimaryListMasterId = ln.MasterId
LEFT OUTER JOIN tblNavigation nav ON nc.NavigationId = nav.NavigationId
INNER JOIN tlkpResourceTypes rt ON rt.ResourceTypeId = res.ResourceTypeId
INNER JOIN tblListContents AS lst ON lst.ListItemMasterId = c.MasterId
WHERE (res.SiteId = @.siteId)
AND (res.ResourceTypeId = 1 OR res.ResourceTypeId = 6)
AND CONTAINS(res.ResourceText, @.query)
AND (c.ContentPublicationDate IS NOT NULL)
AND (c.DeletedBy IS NULL)
AND (c.StatusId = 2)
AND (nc.MasterId IS NOT NULL)
AND ( (@.textOnly=0) or (nav.ExcludeFromTextSite=0 and @.textOnly=1) )
OR
(res.SiteId = @.siteId)
AND (res.ResourceTypeId = 1 OR res.ResourceTypeId = 6)
AND CONTAINS(res.ResourceText, @.query)
AND (c.ContentPublicationDate IS NOT NULL)
AND (c.DeletedBy IS NULL)
AND (c.StatusId = 2)
AND (c.PrimaryListMasterId IS NOT NULL)
AND ( (@.textOnly=0) or (nav.ExcludeFromTextSite=0 and @.textOnly=1) )
ORDER BY rt.SearchResultOrder, c.ContentName

where ResourceText is having a datatype 'Image', so could you tell me how to use that function as well.

i will be waiting for your response soon.

Thanking You.

|||

Are you using Image datatype for storing information .. .

You can't do any Text/String manipulation on Image datatype.

Can I know why you are using image datatype here.

|||

yeah we have taken its an image data type because we have an editor somethning like this in which we are writing but it may have some image as well. so we taken as image data type.

0x4C6F6E646F6E206973206120636974792E3C7370616E207374796C653D22464F4E542D53495A453A2031307074223E203C703EE0A4B5E0A4BEE0A4B2E0A58DE0A4AEE0A580E0A495E0A4BF3C2F703E3C2F7370616E3E

i am having some value like that in Resource Text. so we have an editor complety & we are storing some text as well as possible to stored images as well in editor. but if we find english keyword then it works fine, else it is not suported with hindi keyword. so tell me solution.

|||

HarshalChoksi wrote:

but if we find english keyword then it works fine, else it is not suported with hindi keyword. so tell me solution.

This is not bcs of the SQL server. There is a problem on your Resource Text.

The Unicode english codes & ASCII codes are same. So it will work fine. But your Building the Resource Text somehow failed to create a Unicode text.

You have to look the solution on that area.. Not in SQL Server

Wednesday, March 21, 2012

patindex with nvarchar

No comments:

Post a Comment

Passing result sets

Blog Archive

About Me