CODEWITHSUNDEEP
sqlsql-servert-sql
user1700320
user1700320

Reputation: 11

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

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.

Upvotes: 0

Views: 5887

Answers (5)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

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)

Upvotes: 0

Zhorov
Zhorov

Reputation: 29943

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

Upvotes: 0

iSR5
iSR5

Reputation: 3498

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

Upvotes: 1

Mano
Mano

Reputation: 788

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

Upvotes: 1

Chalavadi Kishore
Chalavadi Kishore

Reputation: 21

enter image description here

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'

Upvotes: 1

Related Questions