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.
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'
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
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,'&','&') ,' ','</Keyword><Keyword>') + '</Keyword></Root>' AS XML) splitted
) D
CROSS APPLY splitted.nodes('/Root/Keyword')m(n)
) C
WHERE
ISNUMERIC(LEFT(splitted, 3)) = 1
(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.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
ORDER BY (SELECT 1) might return randomly. Better to use with v2016+ is OPENJSON (some details here)
STRING_SPLIT ordering is difficult. OPENJSON is good option.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)