Passing result sets: patindex

Showing posts with label patindex. Show all posts

Wednesday, March 21, 2012

PATINDEX, LIKE and escaping

Hi,
I need to scan some character data for the presence of certain "invalid"
characters. The characters I need to scan for are
!@.#$%^&*()_+={}[]|\:;"<>,/?
The PATINDEX function works so long as the ] character is omitted, e.g.
set @.s = 'foo$'
select patindex('%[!@.#$%^&*()_+={}[|\:;"<>,/?]%', @.s)
-- returns 4
Is there any way to tell PATINDEX or LIKE to treat ] as a literal character
within a character set?
Thanks,
DanielDECLARE @.s VARCHAR(20)
SET @.s = 'foo]bar['
SELECT PATINDEX( '%]%', @.s ), PATINDEX( '%[[]%', @.s )
Anith|||Surround the OPENING bracket with brackets.
set @.s = 'foo$'
select patindex('%[!@.#$%^&*()_+={}[[]|\:;"<>,/?]%', @.s)
-- returns 0
"Daniel Pratt" <kolREMOVETHISkata_is@.hotmail.com> wrote in message
news:usIkVir6FHA.1420@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I need to scan some character data for the presence of certain "invalid"
> characters. The characters I need to scan for are
> !@.#$%^&*()_+={}[]|\:;"<>,/?
> The PATINDEX function works so long as the ] character is omitted, e.g.
> set @.s = 'foo$'
> select patindex('%[!@.#$%^&*()_+={}[|\:;"<>,/?]%', @.s)
> -- returns 4
> Is there any way to tell PATINDEX or LIKE to treat ] as a literal
> character within a character set?
> Thanks,
> Daniel
>

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

patindex with nvarchar

Hi Microsoft,

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

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

FROM tblKeywords where keywordID = 68
GO

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

|||

--SQL Function

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Here this is my sp:

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

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

|||

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

I need the Exec spSearch .... statement

|||

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);

}

waiting for your reply.

|||

Please give me any solution for this.

Waiting for your response soon.

|||

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

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

|||

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

PATINDEX to Retrieve data from text field

I am trying to retrieve the data from a table that has text datatype. I just
need to
pull 5 digit numeric value from there which can later be matched with
another table
that has this 5 digit key. Let us assume the table name is Tab1. There are
two
columns col1 containing RecordId and col2 containing text data. I have
created some dummy data to explain my needs:
Col1 Col2
1001 @.:92A:CUSTOMER1@.:97D://XX022211221@.CUSTOMER NAME IS ABC INC.@.SOMETHING
1002 @.:92A:CUSTOMER@.:97A://XX022311229@.CLIENT NAME IS DEF INC.@.SOMETHING
1003 @.:92A:CUST4@.:97B:/XX022511233@.CLIENT NAME IS GHI INC.@.ANYTHING
1004 @.:92A:CUST8@.:97C:XX022311333@.CLIENT NAME IS LKM INC.@.ANYTHING
1005 @.:92A:CUST8@.:97D:22333@.CLIENT NAME IS NOP INC.@.SOMETHING
1006 @.:92A:CUST8@.:97C:CLIENT NAME IS QRS INC.@.ANYTHING
1007 @.:92A:CUST8@.ANYTHING
If I use the following Query which needs to be tuned up to get the right
resultset:
SELECT SUBSTRING(col2, PATINDEX('%@.:97_:%', col2)+14, 5)
from tab1
where PATINDEX('%@.:97_:%', col2) > 0
I get the following results: The top two results are correct but others are
not.
col1 col2
-- --
1001 11221
1002 11229
1003 1233@.
1004 333@.C
1005 IENT
1006 AME I
I need the following resultset from the above data:
Col1 Col2
-- --
1001 11221
1002 11229
1003 11233
1004 11333
1005 22333
Col1 Id 1006 does not have the 5 digit numeric value so it is not required
in the
resultset. Id 1007 does not have :97_C: so this is also not required in the
resultset
too. I will appreciate your help. Thanks in advance. Fraz
Fraz
Look at this example helps you
CREATE FUNCTION dbo.CleanChars
(@.str VARCHAR(8000), @.validchars VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
WHILE PATINDEX('%[^' + @.validchars + ']%',@.str) > 0
SET @.str=REPLACE(@.str, SUBSTRING(@.str ,PATINDEX('%[^' + @.validchars +
']%',@.str), 1) ,'')
RETURN @.str
END
GO
CREATE TABLE sometable
(namestr VARCHAR(20) PRIMARY KEY)
INSERT INTO sometable VALUES ('AB-C123')
INSERT INTO sometable VALUES ('A,B,C')
SELECT namestr,
dbo.CleanChars(namestr,'A-Z 0-9')
FROM sometable
drop table sometable
drop function dbo.CleanChars
"Fraz" <Fraz@.discussions.microsoft.com> wrote in message
news:B964C72E-D1A4-4906-A105-E1D87A2F29D6@.microsoft.com...
> I am trying to retrieve the data from a table that has text datatype. I
just
> need to
> pull 5 digit numeric value from there which can later be matched with
> another table
> that has this 5 digit key. Let us assume the table name is Tab1. There are
> two
> columns col1 containing RecordId and col2 containing text data. I have
> created some dummy data to explain my needs:
> Col1 Col2
> 1001 @.:92A:CUSTOMER1@.:97D://XX022211221@.CUSTOMER NAME IS ABC
INC.@.SOMETHING
> 1002 @.:92A:CUSTOMER@.:97A://XX022311229@.CLIENT NAME IS DEF
INC.@.SOMETHING
> 1003 @.:92A:CUST4@.:97B:/XX022511233@.CLIENT NAME IS GHI INC.@.ANYTHING
> 1004 @.:92A:CUST8@.:97C:XX022311333@.CLIENT NAME IS LKM INC.@.ANYTHING
> 1005 @.:92A:CUST8@.:97D:22333@.CLIENT NAME IS NOP INC.@.SOMETHING
> 1006 @.:92A:CUST8@.:97C:CLIENT NAME IS QRS INC.@.ANYTHING
> 1007 @.:92A:CUST8@.ANYTHING
> If I use the following Query which needs to be tuned up to get the right
> resultset:
> SELECT SUBSTRING(col2, PATINDEX('%@.:97_:%', col2)+14, 5)
> from tab1
> where PATINDEX('%@.:97_:%', col2) > 0
> I get the following results: The top two results are correct but others
are
> not.
> col1 col2
> -- --
> 1001 11221
> 1002 11229
> 1003 1233@.
> 1004 333@.C
> 1005 IENT
> 1006 AME I
> I need the following resultset from the above data:
> Col1 Col2
> -- --
> 1001 11221
> 1002 11229
> 1003 11233
> 1004 11333
> 1005 22333
> Col1 Id 1006 does not have the 5 digit numeric value so it is not required
> in the
> resultset. Id 1007 does not have :97_C: so this is also not required in
the
> resultset
> too. I will appreciate your help. Thanks in advance. Fraz
|||Uri: Thanks for your valuable input. This is a nice function which I am
trying to see if it can fit in my needs. If you could help little more by
showing how I can check to see the 5 digit numbers (11233) between this data
@.:97B:/XX022511233@.CLIENT. The position is always not the same. So by getting
@.:97_: we can get first position and by next "@." we can get second position.
Now I know that my data is in between first and second position and by using
RIGHT function I can get the 5 right digits. Thanks again...Fraz
"Uri Dimant" wrote:

> Fraz
> Look at this example helps you
> CREATE FUNCTION dbo.CleanChars
> (@.str VARCHAR(8000), @.validchars VARCHAR(8000))
> RETURNS VARCHAR(8000)
> BEGIN
> WHILE PATINDEX('%[^' + @.validchars + ']%',@.str) > 0
> SET @.str=REPLACE(@.str, SUBSTRING(@.str ,PATINDEX('%[^' + @.validchars +
> ']%',@.str), 1) ,'')
> RETURN @.str
> END
> GO
> CREATE TABLE sometable
> (namestr VARCHAR(20) PRIMARY KEY)
> INSERT INTO sometable VALUES ('AB-C123')
> INSERT INTO sometable VALUES ('A,B,C')
> SELECT namestr,
> dbo.CleanChars(namestr,'A-Z 0-9')
> FROM sometable
>
> drop table sometable
> drop function dbo.CleanChars
> "Fraz" <Fraz@.discussions.microsoft.com> wrote in message
> news:B964C72E-D1A4-4906-A105-E1D87A2F29D6@.microsoft.com...
> just
> INC.@.SOMETHING
> INC.@.SOMETHING
> are
> the
>
>
|||Hi Fraz,
On Thu, 14 Apr 2005 06:30:05 -0700, Fraz wrote:
(snip)
>I need the following resultset from the above data:
>Col1 Col2
>-- --
>1001 11221
>1002 11229
>1003 11233
>1004 11333
>1005 22333
(snip)
Try the following (note: I added a test case to check that I return the
five digits preceding "@." AFTER the "@.:97_:" marker, not simply the
first five digits followed by "@.").
-- Set up test table and fill it with some rows
create table tab1 (col1 int not null primary key, col2 varchar(200))
go
insert into tab1
select 1001, '@.:92A:CUSTOMER1@.:97D://XX022211221@.CUSTOMER NAME IS ABC
INC.@.SOMETHING'
union all
select 1002, '@.:92A:CUSTOMER@.:97A://XX022311229@.CLIENT NAME IS DEF
INC.@.SOMETHING'
union all
select 1003, '@.:92A:CUST4@.:97B:/XX022511233@.CLIENT NAME IS GHI
INC.@.ANYTHING'
union all
select 1004, '@.:92A:CUST8@.:97C:XX022311333@.CLIENT NAME IS LKM
INC.@.ANYTHING'
union all
select 1005, '@.:92A:CUST8@.:97D:22333@.CLIENT NAME IS NOP INC.@.SOMETHING'
union all
select 1006, '@.:92A:CUST8@.:97C:CLIENT NAME IS QRS INC.@.ANYTHING'
union all
select 1007, '@.:92A:CUST8@.ANYTHING'
union all
select 1008, '@.:92A:CUST44444@.:97B:/XX022511233@.CLIENT NAME IS GHI
INC.@.ANYTHING'
go
-- Here's the code:
SELECT col1,
SUBSTRING(col2,
PATINDEX('%[0-9][0-9][0-9][0-9][0-9]@.%',
SUBSTRING(col2,
PATINDEX('%@.:97_:%', col2),
LEN(col2)))
+ PATINDEX('%@.:97_:%', col2)
- 1,
5)
FROM tab1
WHERE col2 LIKE '%@.:97_:%[0-9][0-9][0-9][0-9][0-9]@.%'
go
-- Done. Now cleanup.
drop table tab1
go
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hello Hugo,
Your code has worked excellently. Most of the 5 digit numbers were correct
except for a few records that were very long and numbers were not correct. I
have dealt with it separately. Thanks a lot for your help. Cheers... Fraz
"Hugo Kornelis" wrote:

> Hi Fraz,
> On Thu, 14 Apr 2005 06:30:05 -0700, Fraz wrote:
> (snip)
> (snip)
> Try the following (note: I added a test case to check that I return the
> five digits preceding "@." AFTER the "@.:97_:" marker, not simply the
> first five digits followed by "@.").
> -- Set up test table and fill it with some rows
> create table tab1 (col1 int not null primary key, col2 varchar(200))
> go
> insert into tab1
> select 1001, '@.:92A:CUSTOMER1@.:97D://XX022211221@.CUSTOMER NAME IS ABC
> INC.@.SOMETHING'
> union all
> select 1002, '@.:92A:CUSTOMER@.:97A://XX022311229@.CLIENT NAME IS DEF
> INC.@.SOMETHING'
> union all
> select 1003, '@.:92A:CUST4@.:97B:/XX022511233@.CLIENT NAME IS GHI
> INC.@.ANYTHING'
> union all
> select 1004, '@.:92A:CUST8@.:97C:XX022311333@.CLIENT NAME IS LKM
> INC.@.ANYTHING'
> union all
> select 1005, '@.:92A:CUST8@.:97D:22333@.CLIENT NAME IS NOP INC.@.SOMETHING'
> union all
> select 1006, '@.:92A:CUST8@.:97C:CLIENT NAME IS QRS INC.@.ANYTHING'
> union all
> select 1007, '@.:92A:CUST8@.ANYTHING'
> union all
> select 1008, '@.:92A:CUST44444@.:97B:/XX022511233@.CLIENT NAME IS GHI
> INC.@.ANYTHING'
> go
> -- Here's the code:
> SELECT col1,
> SUBSTRING(col2,
> PATINDEX('%[0-9][0-9][0-9][0-9][0-9]@.%',
> SUBSTRING(col2,
> PATINDEX('%@.:97_:%', col2),
> LEN(col2)))
> + PATINDEX('%@.:97_:%', col2)
> - 1,
> 5)
> FROM tab1
> WHERE col2 LIKE '%@.:97_:%[0-9][0-9][0-9][0-9][0-9]@.%'
> go
>
> -- Done. Now cleanup.
> drop table tab1
> go
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Fri, 22 Apr 2005 14:21:03 -0700, Fraz wrote:

>Hello Hugo,
>Your code has worked excellently. Most of the 5 digit numbers were correct
>except for a few records that were very long and numbers were not correct. I
>have dealt with it separately. Thanks a lot for your help. Cheers... Fraz
Hi Fraz,
Good to hear that it worked for you. Thanks for reporting back!
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

PATINDEX to Retrieve data from text field

I am trying to retrieve the data from a table that has text datatype. I just
need to
pull 5 digit numeric value from there which can later be matched with
another table
that has this 5 digit key. Let us assume the table name is Tab1. There are
two
columns col1 containing RecordId and col2 containing text data. I have
created some dummy data to explain my needs:
Col1 Col2
1001 @.:92A:CUSTOMER1@.:97D://XX022211221@.CUSTOMER NAME IS ABC INC.@.SOMETHI
NG
1002 @.:92A:CUSTOMER@.:97A://XX022311229@.CLIENT NAME IS DEF INC.@.SOMETHING
1003 @.:92A:CUST4@.:97B:/XX022511233@.CLIENT NAME IS GHI INC.@.ANYTHING
1004 @.:92A:CUST8@.:97C:XX022311333@.CLIENT NAME IS LKM INC.@.ANYTHING
1005 @.:92A:CUST8@.:97D:22333@.CLIENT NAME IS NOP INC.@.SOMETHING
1006 @.:92A:CUST8@.:97C:CLIENT NAME IS QRS INC.@.ANYTHING
1007 @.:92A:CUST8@.ANYTHING
If I use the following Query which needs to be tuned up to get the right
resultset:
SELECT SUBSTRING(col2, PATINDEX('%@.:97_:%', col2)+14, 5)
from tab1
where PATINDEX('%@.:97_:%', col2) > 0
I get the following results: The top two results are correct but others are
not.
col1 col2
-- --
1001 11221
1002 11229
1003 1233@.
1004 333@.C
1005 IENT
1006 AME I
I need the following resultset from the above data:
Col1 Col2
-- --
1001 11221
1002 11229
1003 11233
1004 11333
1005 22333
Col1 Id 1006 does not have the 5 digit numeric value so it is not required
in the
resultset. Id 1007 does not have :97_C: so this is also not required in the
resultset
too. I will appreciate your help. Thanks in advance. FrazFraz
Look at this example helps you
CREATE FUNCTION dbo.CleanChars
(@.str VARCHAR(8000), @.validchars VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
WHILE PATINDEX('%[^' + @.validchars + ']%',@.str) > 0
SET @.str=REPLACE(@.str, SUBSTRING(@.str ,PATINDEX('%[^' + @.validchars +
']%',@.str), 1) ,'')
RETURN @.str
END
GO
CREATE TABLE sometable
(namestr VARCHAR(20) PRIMARY KEY)
INSERT INTO sometable VALUES ('AB-C123')
INSERT INTO sometable VALUES ('A,B,C')
SELECT namestr,
dbo.CleanChars(namestr,'A-Z 0-9')
FROM sometable
drop table sometable
drop function dbo.CleanChars
"Fraz" <Fraz@.discussions.microsoft.com> wrote in message
news:B964C72E-D1A4-4906-A105-E1D87A2F29D6@.microsoft.com...
> I am trying to retrieve the data from a table that has text datatype. I
just
> need to
> pull 5 digit numeric value from there which can later be matched with
> another table
> that has this 5 digit key. Let us assume the table name is Tab1. There are
> two
> columns col1 containing RecordId and col2 containing text data. I have
> created some dummy data to explain my needs:
> Col1 Col2
> 1001 @.:92A:CUSTOMER1@.:97D://XX022211221@.CUSTOMER NAME IS ABC
INC.@.SOMETHING
> 1002 @.:92A:CUSTOMER@.:97A://XX022311229@.CLIENT NAME IS DEF
INC.@.SOMETHING
> 1003 @.:92A:CUST4@.:97B:/XX022511233@.CLIENT NAME IS GHI INC.@.ANYTHING
> 1004 @.:92A:CUST8@.:97C:XX022311333@.CLIENT NAME IS LKM INC.@.ANYTHING
> 1005 @.:92A:CUST8@.:97D:22333@.CLIENT NAME IS NOP INC.@.SOMETHING
> 1006 @.:92A:CUST8@.:97C:CLIENT NAME IS QRS INC.@.ANYTHING
> 1007 @.:92A:CUST8@.ANYTHING
> If I use the following Query which needs to be tuned up to get the right
> resultset:
> SELECT SUBSTRING(col2, PATINDEX('%@.:97_:%', col2)+14, 5)
> from tab1
> where PATINDEX('%@.:97_:%', col2) > 0
> I get the following results: The top two results are correct but others
are
> not.
> col1 col2
> -- --
> 1001 11221
> 1002 11229
> 1003 1233@.
> 1004 333@.C
> 1005 IENT
> 1006 AME I
> I need the following resultset from the above data:
> Col1 Col2
> -- --
> 1001 11221
> 1002 11229
> 1003 11233
> 1004 11333
> 1005 22333
> Col1 Id 1006 does not have the 5 digit numeric value so it is not required
> in the
> resultset. Id 1007 does not have :97_C: so this is also not required in
the
> resultset
> too. I will appreciate your help. Thanks in advance. Fraz|||Uri: Thanks for your valuable input. This is a nice function which I am
trying to see if it can fit in my needs. If you could help little more by
showing how I can check to see the 5 digit numbers (11233) between this data
@.:97B:/XX022511233@.CLIENT. The position is always not the same. So by gettin
g
@.:97_: we can get first position and by next "@." we can get second position.
Now I know that my data is in between first and second position and by using
RIGHT function I can get the 5 right digits. Thanks again...Fraz
"Uri Dimant" wrote:

> Fraz
> Look at this example helps you
> CREATE FUNCTION dbo.CleanChars
> (@.str VARCHAR(8000), @.validchars VARCHAR(8000))
> RETURNS VARCHAR(8000)
> BEGIN
> WHILE PATINDEX('%[^' + @.validchars + ']%',@.str) > 0
> SET @.str=REPLACE(@.str, SUBSTRING(@.str ,PATINDEX('%[^' + @.validchars
+
> ']%',@.str), 1) ,'')
> RETURN @.str
> END
> GO
> CREATE TABLE sometable
> (namestr VARCHAR(20) PRIMARY KEY)
> INSERT INTO sometable VALUES ('AB-C123')
> INSERT INTO sometable VALUES ('A,B,C')
> SELECT namestr,
> dbo.CleanChars(namestr,'A-Z 0-9')
> FROM sometable
>
> drop table sometable
> drop function dbo.CleanChars
> "Fraz" <Fraz@.discussions.microsoft.com> wrote in message
> news:B964C72E-D1A4-4906-A105-E1D87A2F29D6@.microsoft.com...
> just
> INC.@.SOMETHING
> INC.@.SOMETHING
> are
> the
>
>|||Hi Fraz,
On Thu, 14 Apr 2005 06:30:05 -0700, Fraz wrote:
(snip)
>I need the following resultset from the above data:
>Col1 Col2
>-- --
>1001 11221
>1002 11229
>1003 11233
>1004 11333
>1005 22333
(snip)
Try the following (note: I added a test case to check that I return the
five digits preceding "@." AFTER the "@.:97_:" marker, not simply the
first five digits followed by "@.").
-- Set up test table and fill it with some rows
create table tab1 (col1 int not null primary key, col2 varchar(200))
go
insert into tab1
select 1001, '@.:92A:CUSTOMER1@.:97D://XX022211221@.CUSTOMER NAME IS ABC
INC.@.SOMETHING'
union all
select 1002, '@.:92A:CUSTOMER@.:97A://XX022311229@.CLIENT NAME IS DEF
INC.@.SOMETHING'
union all
select 1003, '@.:92A:CUST4@.:97B:/XX022511233@.CLIENT NAME IS GHI
INC.@.ANYTHING'
union all
select 1004, '@.:92A:CUST8@.:97C:XX022311333@.CLIENT NAME IS LKM
INC.@.ANYTHING'
union all
select 1005, '@.:92A:CUST8@.:97D:22333@.CLIENT NAME IS NOP INC.@.SOMETHING'
union all
select 1006, '@.:92A:CUST8@.:97C:CLIENT NAME IS QRS INC.@.ANYTHING'
union all
select 1007, '@.:92A:CUST8@.ANYTHING'
union all
select 1008, '@.:92A:CUST44444@.:97B:/XX022511233@.CLIENT NAME IS GHI
INC.@.ANYTHING'
go
-- Here's the code:
SELECT col1,
SUBSTRING(col2,
PATINDEX('%[0-9][0-9][0-9][0-9][0-9]@.%',
SUBSTRING(col2,
PATINDEX('%@.:97_:%', col2),
LEN(col2)))
+ PATINDEX('%@.:97_:%', col2)
- 1,
5)
FROM tab1
WHERE col2 LIKE '%@.:97_:%[0-9][0-9][0-9][0-9][0-9]@.%'
go
-- Done. Now cleanup.
drop table tab1
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hello Hugo,
Your code has worked excellently. Most of the 5 digit numbers were correct
except for a few records that were very long and numbers were not correct. I
have dealt with it separately. Thanks a lot for your help. Cheers... Fraz
"Hugo Kornelis" wrote:

> Hi Fraz,
> On Thu, 14 Apr 2005 06:30:05 -0700, Fraz wrote:
> (snip)
> (snip)
> Try the following (note: I added a test case to check that I return the
> five digits preceding "@." AFTER the "@.:97_:" marker, not simply the
> first five digits followed by "@.").
> -- Set up test table and fill it with some rows
> create table tab1 (col1 int not null primary key, col2 varchar(200))
> go
> insert into tab1
> select 1001, '@.:92A:CUSTOMER1@.:97D://XX022211221@.CUSTOMER NAME IS ABC
> INC.@.SOMETHING'
> union all
> select 1002, '@.:92A:CUSTOMER@.:97A://XX022311229@.CLIENT NAME IS DEF
> INC.@.SOMETHING'
> union all
> select 1003, '@.:92A:CUST4@.:97B:/XX022511233@.CLIENT NAME IS GHI
> INC.@.ANYTHING'
> union all
> select 1004, '@.:92A:CUST8@.:97C:XX022311333@.CLIENT NAME IS LKM
> INC.@.ANYTHING'
> union all
> select 1005, '@.:92A:CUST8@.:97D:22333@.CLIENT NAME IS NOP INC.@.SOMETHING'
> union all
> select 1006, '@.:92A:CUST8@.:97C:CLIENT NAME IS QRS INC.@.ANYTHING'
> union all
> select 1007, '@.:92A:CUST8@.ANYTHING'
> union all
> select 1008, '@.:92A:CUST44444@.:97B:/XX022511233@.CLIENT NAME IS GHI
> INC.@.ANYTHING'
> go
> -- Here's the code:
> SELECT col1,
> SUBSTRING(col2,
> PATINDEX('%[0-9][0-9][0-9][0-9][0-9]@.
%',
> SUBSTRING(col2,
> PATINDEX('%@.:97_:%', col2),
> LEN(col2)))
> + PATINDEX('%@.:97_:%', col2)
> - 1,
> 5)
> FROM tab1
> WHERE col2 LIKE '%@.:97_:%[0-9][0-9][0-9][0-9][0-9]@.%'
> go
>
> -- Done. Now cleanup.
> drop table tab1
> go
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||On Fri, 22 Apr 2005 14:21:03 -0700, Fraz wrote:

>Hello Hugo,
>Your code has worked excellently. Most of the 5 digit numbers were correct
>except for a few records that were very long and numbers were not correct.
I
>have dealt with it separately. Thanks a lot for your help. Cheers... Fraz
Hi Fraz,
Good to hear that it worked for you. Thanks for reporting back!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

patindex that I can use in RS?

Basically, I am trying this in a table cell expression:
=iif(patindex('%-%', Fields!courseName.Value) <>
0,left(Fields!courseName.Value, patindex('%-%',
Fields!courseName.Value)-1),Fields!courseName.Value)
It errors because it says patindex is not declared. I tried doing this part
in my query in a stored proc instead but was unable to get the results I
needed, so thought I would try in RS instead.
Is there any way for me to do this in RS? Basically in my stored proc I was
trying to do:
Query where patindex <> 0
Union
Query where patindex = 0
The field I am trying to fix is basically: â'Name â' explanationâ' and all I
want is the name before the â?...â'. But there are some fields with just â'Nameâ'
and no dash or explanation. With my union statement, instead of getting:
Name1
Name2
Name3
I get
Name1
Name1 â' Explanation
Name2
Name2 - Explanation
Name3
Name3 â' Explanation
The query I am using now gives me the results I want (correct records) and I
am trying to split out the name in RS.
Thank you for your time and help!WHen you are in an expression you are talking VB.NET...
Instr is the function you are looking for...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"SharinDenver" wrote:
> Basically, I am trying this in a table cell expression:
> =iif(patindex('%-%', Fields!courseName.Value) <>
> 0,left(Fields!courseName.Value, patindex('%-%',
> Fields!courseName.Value)-1),Fields!courseName.Value)
> It errors because it says patindex is not declared. I tried doing this part
> in my query in a stored proc instead but was unable to get the results I
> needed, so thought I would try in RS instead.
> Is there any way for me to do this in RS? Basically in my stored proc I was
> trying to do:
> Query where patindex <> 0
> Union
> Query where patindex = 0
> The field I am trying to fix is basically: â'Name â' explanationâ' and all I
> want is the name before the â?...â'. But there are some fields with just â'Nameâ'
> and no dash or explanation. With my union statement, instead of getting:
> Name1
> Name2
> Name3
> I get
> Name1
> Name1 â' Explanation
> Name2
> Name2 - Explanation
> Name3
> Name3 â' Explanation
> The query I am using now gives me the results I want (correct records) and I
> am trying to split out the name in RS.
> Thank you for your time and help!
>

Tuesday, March 20, 2012

PatIndex Pattern

I am attempting to use PatIndex to find characters outside of the range of
character codes 32 - 126, or in other words, find all characters in the
ranges of 0 - 31 and 127 - 255. I have written the following so far:
DECLARE @.str varchar(1000)
SET @.str = '\[%]ZNORMAL 123? 0
0
jwH1w..0 j)?0 '
SELECT
PATINDEX('%[^ !"#$%&()*+,-./0123456789:;<=>?@.ABCDEFGHIJKLMNOPQRSTUVWXYZ\
`abcdefghijklmnopqrstuvwxyz]%', @.str)
How would I include the following characters in the pattern search: '^[]
%
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200605/1I have worked on this problem a bit in the past, and never found a way to
escape all of those characters properly in a PATINDEX pattern. The only
solution I came up with--which is somewhat suboptimal--is to write a UDF
that uses LIKE and evaluates each character in the string. LIKE has an
optional ESCAPE argument that lets this solution work:
CREATE FUNCTION EscapedPATINDEX
(
@.Pattern VARCHAR(200),
@.String VARCHAR(8000),
@.Escape CHAR(1)
)
RETURNS INT
AS
BEGIN
DECLARE @.return INT
SELECT @.return = MIN(Number)
FROM Numbers
WHERE
number >= 1
AND number <= DATALENGTH(@.String)
AND SUBSTRING(@.String, number, 1) LIKE @.Pattern ESCAPE @.Escape
RETURN (@.Return)
END
GO
--
This UDF allows you to do, e.g.:
SELECT dbo.EscapedPATINDEX('[^ \^]', '^^^c^^^', '')
--Returns 4
--
Note that this UDF requires a table of numbers. See the following link
if you don't already have one:
http://sqljunkies.com/WebLog/amacha...mbersTable.aspx
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"cbrichards" <u3288@.uwe> wrote in message news:5fcd316e41596@.uwe...
>I am attempting to use PatIndex to find characters outside of the range of
> character codes 32 - 126, or in other words, find all characters in the
> ranges of 0 - 31 and 127 - 255. I have written the following so far:
> DECLARE @.str varchar(1000)
> SET @.str = '\[%]ZNORMAL 123? 0?
> 0?
> jwH1w..0 j)?0 '
> SELECT
> PATINDEX('%[^ !"#$%&()*+,-./0123456789:;<=>?@.ABCDEFGHIJKLMNOPQRSTUVWXY
Z\
> `abcdefghijklmnopqrstuvwxyz]%', @.str)
> How would I include the following characters in the pattern search: '^[
;]%
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200605/1|||Thanks Adam. Works great!
Adam Machanic wrote:[vbcol=seagreen]
>I have worked on this problem a bit in the past, and never found a way to
>escape all of those characters properly in a PATINDEX pattern. The only
>solution I came up with--which is somewhat suboptimal--is to write a UDF
>that uses LIKE and evaluates each character in the string. LIKE has an
>optional ESCAPE argument that lets this solution work:
>--
>CREATE FUNCTION EscapedPATINDEX
>(
> @.Pattern VARCHAR(200),
> @.String VARCHAR(8000),
> @.Escape CHAR(1)
> )
>RETURNS INT
>AS
>BEGIN
> DECLARE @.return INT
> SELECT @.return = MIN(Number)
> FROM Numbers
> WHERE
> number >= 1
> AND number <= DATALENGTH(@.String)
> AND SUBSTRING(@.String, number, 1) LIKE @.Pattern ESCAPE @.Escape
> RETURN (@.Return)
>END
>GO
>--
> This UDF allows you to do, e.g.:
>--
>SELECT dbo.EscapedPATINDEX('[^ \^]', '^^^c^^^', '')
>--Returns 4
>--
> Note that this UDF requires a table of numbers. See the following link
>if you don't already have one:
>http://sqljunkies.com/WebLog/amacha...mbersTable.aspx
>
>[quoted text clipped - 10 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200605/1

PatIndex Pattern

I am attempting to use PatIndex to find characters outside of the range of
character codes 32 - 126, or in other words, find all characters in the
ranges of 0 - 31 and 127 - 255. I have written the following so far:
DECLARE @.str varchar(1000)
SET @.str = '\[%]ZNORMAL 123? 0?å
0?å
jûwH1øwÿ..0 j)ß©0 '
SELECT
PATINDEX('%[^ !"#$%&()*+,-./0123456789:;<=>?@.ABCDEFGHIJKLMNOPQRSTUVWXYZ\
`abcdefghijklmnopqrstuvwxyz]%', @.str)
How would I include the following characters in the pattern search: '^[]%
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1I have worked on this problem a bit in the past, and never found a way to
escape all of those characters properly in a PATINDEX pattern. The only
solution I came up with--which is somewhat suboptimal--is to write a UDF
that uses LIKE and evaluates each character in the string. LIKE has an
optional ESCAPE argument that lets this solution work:
--
CREATE FUNCTION EscapedPATINDEX
(
@.Pattern VARCHAR(200),
@.String VARCHAR(8000),
@.Escape CHAR(1)
)
RETURNS INT
AS
BEGIN
DECLARE @.return INT
SELECT @.return = MIN(Number)
FROM Numbers
WHERE
number >= 1
AND number <= DATALENGTH(@.String)
AND SUBSTRING(@.String, number, 1) LIKE @.Pattern ESCAPE @.Escape
RETURN (@.Return)
END
GO
--
This UDF allows you to do, e.g.:
--
SELECT dbo.EscapedPATINDEX('[^ \^]', '^^^c^^^', '\')
--Returns 4
--
Note that this UDF requires a table of numbers. See the following link
if you don't already have one:
http://sqljunkies.com/WebLog/amachanic/articles/NumbersTable.aspx
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"cbrichards" <u3288@.uwe> wrote in message news:5fcd316e41596@.uwe...
>I am attempting to use PatIndex to find characters outside of the range of
> character codes 32 - 126, or in other words, find all characters in the
> ranges of 0 - 31 and 127 - 255. I have written the following so far:
> DECLARE @.str varchar(1000)
> SET @.str = '\[%]ZNORMAL 123? 0?å
> 0?å
> jûwH1øwÿ..0 j)ß©0 '
> SELECT
> PATINDEX('%[^ !"#$%&()*+,-./0123456789:;<=>?@.ABCDEFGHIJKLMNOPQRSTUVWXYZ\
> `abcdefghijklmnopqrstuvwxyz]%', @.str)
> How would I include the following characters in the pattern search: '^[]%
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||Thanks Adam. Works great!
Adam Machanic wrote:
>I have worked on this problem a bit in the past, and never found a way to
>escape all of those characters properly in a PATINDEX pattern. The only
>solution I came up with--which is somewhat suboptimal--is to write a UDF
>that uses LIKE and evaluates each character in the string. LIKE has an
>optional ESCAPE argument that lets this solution work:
>--
>CREATE FUNCTION EscapedPATINDEX
>(
> @.Pattern VARCHAR(200),
> @.String VARCHAR(8000),
> @.Escape CHAR(1)
>)
>RETURNS INT
>AS
>BEGIN
> DECLARE @.return INT
> SELECT @.return = MIN(Number)
> FROM Numbers
> WHERE
> number >= 1
> AND number <= DATALENGTH(@.String)
> AND SUBSTRING(@.String, number, 1) LIKE @.Pattern ESCAPE @.Escape
> RETURN (@.Return)
>END
>GO
>--
> This UDF allows you to do, e.g.:
>--
>SELECT dbo.EscapedPATINDEX('[^ \^]', '^^^c^^^', '\')
>--Returns 4
>--
> Note that this UDF requires a table of numbers. See the following link
>if you don't already have one:
>http://sqljunkies.com/WebLog/amachanic/articles/NumbersTable.aspx
>>I am attempting to use PatIndex to find characters outside of the range of
>> character codes 32 - 126, or in other words, find all characters in the
>[quoted text clipped - 10 lines]
>> How would I include the following characters in the pattern search: '^[]%
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1

PATINDEX not working with "[" ?

For some reason I can't get PATINDEX to find instances of the opening
bracket "[" although it finds the closing bracket "]" without any trouble.
Running the following script results in "0" for brakbegin and the correct
number for brakend.
UPDATE FOIdata
Set
brakbegin = PatIndex('%[%',Title_txt),
brakend = PatIndex('%]%',Title_txt)
Table Structure:
Title_txt text = "Oncovin (Lilly Research) [Leukemia] 12/04/1984 Medical
Officers Review"
brakbegin int
brakend int
Any idea what I'm doing wrong?
Terrytry this
declare @.Var varchar(50)
select @.var ='abc[def]zzz'
select PatIndex('%[[]%',@.var)
http://sqlservercode.blogspot.com/|||I don't know why this is happening but you could use CHARINDEX instead.
Declare @.text varchar(50)
Set @.Text = 'Oncovin (Lilly Research) [Leukemia] 12/04/1984 Medical
Officers Review'
Select charindex('[', @.Text)
Select Charindex(']', @.text, (charindex('[', @.Text)))
HTH
Barry|||Escape it:
PatIndex('%[]]%',Title_txt)
ML
http://milambda.blogspot.com/|||Thanks for all your excellent, quick help!
I didn't know about escaping the open bracket. That did the trick!
Thanks again!
Terry

PATINDEX fails to find (UK Pound Sterling symbol)

Hi all, Has anyone ever tried looking for a UK Pound sign using the PATINDEX function? It's fine for other characters but fails to find
The following code returns -1 because the function does not locate the pound symbol (it is in there!)
SELECT @.intPos = PATINDEX('%%', [my_text_field]) FROM my_table
Any suggestions?
Many thanks!I just tried a little test...

declare @.v1 varchar(10), @.v2 nvarchar(20)
select @.v1 = '100.00', @.v2 = '100.00'
select @.v1, @.v2
select PATINDEX('%%', @.v1),PATINDEX('%%', @.v2)

and got

---- -------
100.00 100.00

---- ----
1 1

do you have anymore details?|||Ah, yes your test certainly works! Your test spurred me to rey something out...Indeed I've found out why my code was not working...

It turned out that the data I was accessing contained a unc pound character amongst the text, this looked correct in my WinXP Notepad but manifested as two odd characters when viewed in sql server, that's why my search for a normal pond character was failing!

Thanks for you help!|||sometimes it's hard to see the forrest because of all the trees! Gald to help!

PATINDEX and CHARINDEX

Hello
I have a table in my database , in one field i have data like this
(230+365+651+695) varchar(100)
I want to use the any SQL function to return the data in form of rows from
the above field.
Result should look like
230
365
651
695
Anyone can help in this regard'?
Thanks in advance.Here's one way to do it using an auxiliary table of numbers:
SET NOCOUNT ON;
USE tempdb; -- specify your user db here
GO
IF OBJECT_ID('dbo.Arrays') IS NOT NULL
DROP TABLE dbo.Arrays;
GO
CREATE TABLE dbo.Arrays
(
arrid VARCHAR(5) NOT NULL PRIMARY KEY,
arr VARCHAR(1000) NOT NULL
);
INSERT INTO dbo.Arrays VALUES('A', '230+365+651+695');
INSERT INTO dbo.Arrays VALUES('B', '1+23+456');
GO
-- Code to create and populate the auxiliary table of numbers:
IF OBJECT_ID('dbo.Nums') IS NOT NULL
DROP TABLE dbo.Nums;
GO
CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);
DECLARE @.max AS INT, @.rc AS INT;
SET @.max = 8000; -- adjust @.max to your needs
SET @.rc = 1;
INSERT INTO dbo.Nums VALUES(1);
WHILE @.rc * 2 <= @.max
BEGIN
INSERT INTO dbo.Nums SELECT n + @.rc FROM dbo.Nums;
SET @.rc = @.rc * 2;
END
INSERT INTO dbo.Nums SELECT n + @.rc FROM dbo.Nums WHERE n + @.rc <= @.max;
GO
-- Query that splits arrays
SELECT A.arrid,
Nums.n - LEN(REPLACE(LEFT(A.arr, Nums.n), '+', '')) + 1 AS pos,
CAST(SUBSTRING(A.arr, Nums.n,
CHARINDEX('+', A.arr + '+', Nums.n) - Nums.n)
AS INT) AS element
FROM dbo.Arrays AS A
JOIN dbo.Nums
ON Nums.n <= LEN(A.arr) AND SUBSTRING('+' + A.arr, Nums.n, 1) = '+';
GO
Output:
arrid pos element
-- -- --
A 1 230
A 2 365
A 3 651
A 4 695
B 1 1
B 2 23
B 3 456
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"mukki_neo" <u20644@.uwe> wrote in message news:5e741dc37de34@.uwe...
> Hello
> I have a table in my database , in one field i have data like this
> (230+365+651+695) varchar(100)
> I want to use the any SQL function to return the data in form of rows from
> the above field.
> Result should look like
> 230
> 365
> 651
> 695
> Anyone can help in this regard'?
> Thanks in advance.|||Thanks a lot for quick response.
It just work fine with my Database, but what if i have a value of
"Alphanumeric" in this field... like
(DO-903+DP-366+DP-659+TM-TEMP)
same for other rows corresponding columns
i want to show it like
DO-903
DP-366
DP-659
TM-TEMP
Any help please?
Itzik Ben-Gan wrote:
>Here's one way to do it using an auxiliary table of numbers:
>SET NOCOUNT ON;
>USE tempdb; -- specify your user db here
>GO
>IF OBJECT_ID('dbo.Arrays') IS NOT NULL
> DROP TABLE dbo.Arrays;
>GO
>CREATE TABLE dbo.Arrays
>(
> arrid VARCHAR(5) NOT NULL PRIMARY KEY,
> arr VARCHAR(1000) NOT NULL
> );
>INSERT INTO dbo.Arrays VALUES('A', '230+365+651+695');
>INSERT INTO dbo.Arrays VALUES('B', '1+23+456');
>GO
>-- Code to create and populate the auxiliary table of numbers:
>IF OBJECT_ID('dbo.Nums') IS NOT NULL
> DROP TABLE dbo.Nums;
>GO
>CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);
>DECLARE @.max AS INT, @.rc AS INT;
>SET @.max = 8000; -- adjust @.max to your needs
>SET @.rc = 1;
>INSERT INTO dbo.Nums VALUES(1);
>WHILE @.rc * 2 <= @.max
>BEGIN
> INSERT INTO dbo.Nums SELECT n + @.rc FROM dbo.Nums;
> SET @.rc = @.rc * 2;
>END
>INSERT INTO dbo.Nums SELECT n + @.rc FROM dbo.Nums WHERE n + @.rc <= @.max;
>GO
>-- Query that splits arrays
>SELECT A.arrid,
> Nums.n - LEN(REPLACE(LEFT(A.arr, Nums.n), '+', '')) + 1 AS pos,
> CAST(SUBSTRING(A.arr, Nums.n,
> CHARINDEX('+', A.arr + '+', Nums.n) - Nums.n)
> AS INT) AS element
>FROM dbo.Arrays AS A
> JOIN dbo.Nums
> ON Nums.n <= LEN(A.arr) AND SUBSTRING('+' + A.arr, Nums.n, 1) = '+';
>GO
>Output:
>arrid pos element
>-- -- --
>A 1 230
>A 2 365
>A 3 651
>A 4 695
>B 1 1
>B 2 23
>B 3 456
>
>[quoted text clipped - 12 lines]|||I changed the CAST from INT to VARCHAR, and it works fine, am i right?
Itzik Ben-Gan wrote:
>Here's one way to do it using an auxiliary table of numbers:
>SET NOCOUNT ON;
>USE tempdb; -- specify your user db here
>GO
>IF OBJECT_ID('dbo.Arrays') IS NOT NULL
> DROP TABLE dbo.Arrays;
>GO
>CREATE TABLE dbo.Arrays
>(
> arrid VARCHAR(5) NOT NULL PRIMARY KEY,
> arr VARCHAR(1000) NOT NULL
> );
>INSERT INTO dbo.Arrays VALUES('A', '230+365+651+695');
>INSERT INTO dbo.Arrays VALUES('B', '1+23+456');
>GO
>-- Code to create and populate the auxiliary table of numbers:
>IF OBJECT_ID('dbo.Nums') IS NOT NULL
> DROP TABLE dbo.Nums;
>GO
>CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);
>DECLARE @.max AS INT, @.rc AS INT;
>SET @.max = 8000; -- adjust @.max to your needs
>SET @.rc = 1;
>INSERT INTO dbo.Nums VALUES(1);
>WHILE @.rc * 2 <= @.max
>BEGIN
> INSERT INTO dbo.Nums SELECT n + @.rc FROM dbo.Nums;
> SET @.rc = @.rc * 2;
>END
>INSERT INTO dbo.Nums SELECT n + @.rc FROM dbo.Nums WHERE n + @.rc <= @.max;
>GO
>-- Query that splits arrays
>SELECT A.arrid,
> Nums.n - LEN(REPLACE(LEFT(A.arr, Nums.n), '+', '')) + 1 AS pos,
> CAST(SUBSTRING(A.arr, Nums.n,
> CHARINDEX('+', A.arr + '+', Nums.n) - Nums.n)
> AS INT) AS element
>FROM dbo.Arrays AS A
> JOIN dbo.Nums
> ON Nums.n <= LEN(A.arr) AND SUBSTRING('+' + A.arr, Nums.n, 1) = '+';
>GO
>Output:
>arrid pos element
>-- -- --
>A 1 230
>A 2 365
>A 3 651
>A 4 695
>B 1 1
>B 2 23
>B 3 456
>
>[quoted text clipped - 12 lines]|||No need to cast it if you want to keep it a character string.
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"mukki_neo" <u20644@.uwe> wrote in message news:5e757a18834f7@.uwe...
>I changed the CAST from INT to VARCHAR, and it works fine, am i right?
> Itzik Ben-Gan wrote:

Wednesday, March 21, 2012

PATINDEX, LIKE and escaping

patindex with nvarchar

patindex with nvarchar

PATINDEX to Retrieve data from text field

PATINDEX to Retrieve data from text field

patindex that I can use in RS?

Tuesday, March 20, 2012

PatIndex Pattern

PatIndex Pattern

PATINDEX not working with "[" ?

PATINDEX fails to find (UK Pound Sterling symbol)

PATINDEX and CHARINDEX

Passing result sets

Blog Archive

About Me