Get dynamic length value in a string after a specific sub-string,

Question

Lets say I have a table like this:

--------------------------------------------------
| id | text                                      |
--------------------------------------------------
| 01 | Test string <div src="0124">              |
--------------------------------------------------
| 02 | Another type <div src="667"> test string  |
--------------------------------------------------

I need to write a SELECT that gets the value that is inside src="" from the string in the text column. So My end result should be:

------
|text|
------
|0124|
-----
| 667|

In my case SUBSTRING does not suit me, since I don't know my sub-strings

Also since I'm looking for a value, that comes after a specific sub-string, I can't use char position.

So how can I get a value that comes after a specific sub-string inside a string

If you want to get the numeric values from a given string, you can try to use the regular expression kind of thing. PS: From the given examples, I thought you can use the regex if you want to get only numeric values. — Srinivas V., Apr 25 '16 at 10:40
The value I want will be numeric, but I tend to avoid regular expressions, since those are hard to understand and even harder to learn — Stralos, Apr 25 '16 at 10:49

Dustin · Accepted Answer · 2016-04-26T10:15:35.053

4

You can use SUBSTRING by finding the initial position of the number and then finding the length of the string:

SELECT SUBSTRING(text, (CHARINDEX('src=', text) + 5), (CHARINDEX(CHAR(34) + '>', text) - (CHARINDEX('src=', text) + 5))) AS text
FROM yourTable;

This will get your starting postion (notice I add 5 to it):

(CHARINDEX('src=', text) + 5)

The following will get your length:

(CHARINDEX(CHAR(34) + '>', text) - (CHARINDEX('src=', text) + 5))

edited Apr 26 '16 at 10:15

answered Apr 25 '16 at 10:45

Dustin

462
1
6
19

(CHARINDEX(CHAR(34) + '>') requires 2 to 3 parameters. – Stralos Apr 26 '16 at 08:30
aswesome! One more thing. Lets say my text string would contain multiple 'src=' instances. What then (since CharIndex returns only the first instance.). I know this is a bit more then the question asked. – Stralos Apr 26 '16 at 10:27
Using this method, that may be difficult to do. If you knew you had two instances of "src=", for example, you might be able to do a UNION between two SELECT statements, where you are using the starting position (the third parameter) for the CHARINDEX as something after the first instance of "src=". The easier way would be to make sure there is only one instance in your table (for each row) to begin with to make sure you are getting all of your values – Dustin Apr 26 '16 at 10:38
yeh... well sadly there could be an N amount of instances, and I have no control over that... – Stralos Apr 26 '16 at 10:41
do you have control over making the table? – Dustin Apr 26 '16 at 10:42
Also this might be helpful - it finds all occurences of a substring: http://stackoverflow.com/questions/8726111/sql-server-find-nth-occurrence-in-a-string This would go definitely go beyond the scope of the question, though! – Dustin Apr 26 '16 at 10:45
if by making a table you mean creating temporary tables/views, then yes. But I can't edit the excising table, that has this mess – Stralos Apr 26 '16 at 10:48

Jande · Answer 2 · 2016-04-25T10:52:23.107

Create function to get numeric

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

Call it

/* Run the UDF with different test values */
SELECT dbo.udf_GetNumeric('') AS 'EmptyString';
SELECT dbo.udf_GetNumeric('asdf1234a1s2d3f4@@@') AS 'asdf1234a1s2d3f4@@@';
SELECT dbo.udf_GetNumeric('123456') AS '123456';
SELECT dbo.udf_GetNumeric('asdf') AS 'asdf';
SELECT dbo.udf_GetNumeric(NULL) AS 'NULL';

--In your case 

SELECT id, dbo.udf_GetNumeric([text]) from table
GO

Beware functions called on every row can be non-performant. Consider CROSS APPLY. — Crowcoder, Apr 25 '16 at 10:49

score 0 · Answer 3 · answered Apr 25 '16 at 10:44

You can try following script where SQL string function CHARINDEX is used in a SQL CTE expression

-- create table tblHTML (id varchar(10), [text] nvarchar(max))
-- insert into tblHTML select '01',N'Test string <div src="0124">              '
-- insert into tblHTML select '02',N'Another type <div src="667"> test string  '
;with cte as (
    select
        *, 
        CHARINDEX('src="',[text],1)+len('src="') start,
        CHARINDEX('"',[text], CHARINDEX('src="',[text],1)+len('src="')) finish
    from tblHTML
)
select
    *, SUBSTRING([text], start, finish-start) src
from cte

score 0 · Answer 4 · answered Apr 25 '16 at 10:47

You can try this query. It uses Substring with charindex to figure out the pattern start point and end point and gets everything in between.

--create table #temp (id int, text nvarchar(max))
--insert into #temp
--values
--(1, 'Test string <div src="0124">'),
--(2, 'Another type <div src="667"> test string')
declare @pattern nvarchar(max), @patternend nvarchar(max)
set @pattern='<div src="'
set @patternend='"'
select 
id,
substring(text,charindex(@pattern,text)+len(@pattern), charindex(@patternend,text,charindex(@pattern,text)+len(@pattern))-charindex(@pattern,text)-len(@pattern))
as text from #temp

score 0 · Answer 5 · answered Apr 25 '16 at 10:49

SQL Server is not really good at string operations, particularly for extracting values. Nesting string functions can product messy code. One method around this is to use outer apply:

select t2.val
from t outer apply
     (select stuff(t.text, 1, charindex('src="', t.text) + 4, '') as text1
     ) t1 outer apply
     (select left(t1.text1, charindex('"', text1) - 1) as val) t2;

Note: If the value does not occur in all text fields, you'll need somewhat more complex logic (case) statements. This answer is intended to show one way of expressing string logic.

Get dynamic length value in a string after a specific sub-string,

5 Answers5