How to find multiple occurrence of particular string and fetch value in SQL Server

Question

I have string with patterns and I wan to get values from each row in table.

For example:

declare @str = 'abcasd khgf [img]123-456-789" kh kshgdf sfj sfg [img]354-658-598" style asdlkafl'

Now I want to get only numbers after each [img] and store it into temp table.

Temp table output required

id   number
-----------------
1    123-456-789
2    354-658-598

string @str may contain more than 2 number and may have additional unwanted string.

score 1 · Answer 1 · edited Feb 08 '19 at 12:33

1

CREATE FUNCTION dbo.udf_GetNumeric
(@strAlphaNumeric VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @intAlpha INT
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)
BEGIN
WHILE @intAlpha > 0
BEGIN
SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )
END
END
RETURN ISNULL(@strAlphaNumeric,0)
END
GO

SELECT dbo.udf_GetNumeric('abcasd khgf [img]123-456-789" kh kshgdf sfj sfg [img]354-658-598" style asdlkafl') as 'Name'

edited Feb 08 '19 at 12:33

Zhorov

28,486
6
27
52

answered Feb 08 '19 at 12:30

Chalavadi Kishore

21
6

Oh no... please avoid loops if ever possible. And please avoid scalar functions. Both approaches are well known as speed killers... – Shnugo Feb 08 '19 at 15:04

score 1 · Answer 2 · answered Feb 08 '19 at 12:34

You can try this query. Here you need to use split function before executing this query make sure you have the split function.

DECLARE @str VARCHAR(100)
DECLARE @tempTable1 TABLE(ID INT,stringValue VARCHAR(250))
DECLARE @tempTable2 TABLE(ID INT IDENTITY,Numbers VARCHAR(250))

SET @str='abcasd khgf [img]123-456-789" kh kshgdf sfj sfg [img]354-658-598"'
INSERT INTO @tempTable1
SELECT * FROM  [dbo].[Split](@str,'[img]')

WHILE 0<(SELECT COUNT(*) from @tempTable1)
BEGIN
DECLARE @strVal varchar(250)=''
SELECT TOP 1 @strVal= stringValue from @tempTable1
DECLARE @intAlpha INT
SET @intAlpha = PATINDEX('%[^0-9.-]%', @strVal)
BEGIN
WHILE @intAlpha > 0
BEGIN
SET @strVal = STUFF(@strVal, @intAlpha, 1, '' )
SET @intAlpha = PATINDEX('%[^0-9.-]%', @strVal )
END
IF @strVal<>''
INSERT INTO @tempTable2 values(@strVal)
END
DELETE TOP (1) FROM @tempTable1
END
SELECT * FROM @tempTable2

please avoid loops... They are awfully slow and point to *procedural* thinking, while T-SQL demands for *set-based* approaches... — Shnugo, Feb 08 '19 at 15:12

score 1 · Answer 3 · answered Feb 08 '19 at 12:38

you can use XML to split the string into rows and then use REPLACE() to remove all extra special characters as much as needed to clean up your output.

DECLARE @str VARCHAR(MAX)  ='abcasd khgf [img]123-456-789" kh kshgdf sfj sfg [img]354-658-598" style asdlkafl'

SELECT 
    ROW_NUMBER() OVER(ORDER BY splitted) id 
,   splitted number
FROM (
    SELECT 
        REPLACE(REPLACE(LTRIM(RTRIM(m.n.value('.[1]','VARCHAR(8000)'))) , '[img]',''), '"','') splitted
    FROM (
        SELECT CAST('<Root><Keyword>' + REPLACE(REPLACE(@str,'&','&amp;') ,' ','</Keyword><Keyword>') + '</Keyword></Root>' AS XML) splitted
    ) D
    CROSS APPLY splitted.nodes('/Root/Keyword')m(n)
) C
WHERE 
    ISNUMERIC(LEFT(splitted, 3)) = 1

The idea is fine, but: 1) If you expect forbidden characters use `(SELECT @str AS [*] FOR XML PATH(''))` This will escape all forbidden characters implicitly. 2) Use the `[img]` as delimiter, otherwise you might find some other junk... 3) It is a bit dangerous to rely on `ORDER BY splitted`. This will not reflect the original order. And this can be done with less steps. — Shnugo, Feb 08 '19 at 15:11

score 0 · Answer 4 · answered Feb 08 '19 at 14:20

0

Another possible approach, if you have SQL Server 2016 or higher, is to use STRING_SPLIT() function. The input string is splitted using ' ' as delimiter and only rows containing [img] are selected.

DECLARE @str varchar(max)
SET @str = 'abcasd khgf [img]123-456-789" kh kshgdf sfj sfg [img]354-658-598" style asdlkafl'

SELECT
   ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS Id,
   REPLACE(REPLACE([value], '[img]', ''), '"', '') AS [Number]
FROM STRING_SPLIT(@str, ' ')
WHERE CHARINDEX('[img]', [value]) = 1

Output:

Id  Number
1   123-456-789
2   354-658-598

answered Feb 08 '19 at 14:20

Zhorov

28,486
6
27
52

We don't know, if the internal sort order is important, but `ORDER BY (SELECT 1)` might return randomly. Better to use with v2016+ is `OPENJSON` ([some details here](https://stackoverflow.com/a/51401153/5089204)) – Shnugo Feb 08 '19 at 15:15
@Shnugo Yes, you are right, with `STRING_SPLIT` ordering is difficult. `OPENJSON` is good option. – Zhorov Feb 08 '19 at 15:24

score 0 · Answer 5 · answered Feb 08 '19 at 15:03

I'd try it like this

DECLARE @str VARCHAR(MAX)  ='abcasd khgf [img]123-456-789" kh kshgdf sfj sfg [img]354-658-598" style asdlkafl';

SELECT LEFT(imgResolved,CHARINDEX('"',imgResolved)-1) TheNumber
FROM (SELECT CAST('<x>' + REPLACE(SUBSTRING(@str,CHARINDEX('[img]',@str)+5,LEN(@str)),'[img]','</x><x>') + '</x>' AS XML)) A(x)
CROSS APPLY x.nodes('/x') B(img)
CROSS APPLY(SELECT img.value('text()[1]','nvarchar(max)')) C(imgResolved)

How to find multiple occurrence of particular string and fetch value in SQL Server

5 Answers5