Find a numeric group in the string

Question

Hello, I would like a help to locate a group of 20 numbers within a string that has characters and symbols. Let’s say my string has 165 characters, I want the numbers from 127 to 147 that are my group "00001138271467122784", but the size of the string on the other lines is varied, my number can start before or after, the order of beginning and end is not fixed. I have '_' to be able to delimit, but how? SQL Server Management Studio

Answer 1

Here is demonstration of the use of the function charindex to find the section you are looking for.

 -- código #1
declare @URL varchar(2000);
set @URL= 'file%3A%2F%2F%2Fmnt%2FABOX%2FRESULTS%2Fresults%2Fresults%2Ft‌​xt%2F201606281059520‌​00_083021988480019_3‌​8505_4524425_170_223‌​9408_>>>000011382714‌​67122784<<<<_41_53_A‌​TENDENTE.txt';

PRINT charindex('00001138271467122784', @URL);

The code to search in the rows of a table can be something like:

-- código #2
declare @Txt char(20);
set @Txt= '00001138271467122784';

SELECT URL, 
       [Posição]= charindex(@Txt, URL)
  from tabela;

---

UPDATING

I have just discovered a pattern the sets I want are between the sixth '_' and the antepenultimate '_' (9 of the count) always so, so I need something to take their position, the sixth and the ninth '_' and between them is the field I want.

Considering the additional explanations about the "_" separator, one can create a specific function to obtain the content between the sixth and ninth "_".

-- código #4 v3
CREATE FUNCTION analisaURL (@pURL varchar(2000))
       returns @pToken varchar(200) as
begin
declare @I int, @Pos6 int, @Pos9 int;

-- obtém sexto "_"
set @Pos6= 0;
set @I= 0;
while @I <> 6
  begin
  set @Pos6= charindex('_', @pURL, (@Pos6 +1));
  set @I += 1;
  end;

-- obtém nono "_"
set @Pos9= @Pos6;
while @I <> 9
  begin
  set @Pos9= charindex('_', @pURL, (@Pos9 +1));
  set @I += 1;
  end;

return substring(@pURL, (@Pos6 +1), (@Pos9 - @Pos6 -1));
end;
go

Carefully evaluate the function code as it has not been tested. It is recommended to add error handling to the function.

The use of the function is thus:

-- código #5
SELECT T.URL, dbo.analisaURL(T.URL) as Token
  from tabela as T;

---

Another option is to use type function split string to split the URL into parts and then select only the seventh part of each URL.

-- código #3 v2
with cteSplitURL as (
SELECT T.URL, S.ItemNumber as seq, S.Item as token
  from tabela as T
       outer apply dbo.SplitURL(T.URL, '_') as S
)
SELECT URL, token
  from cteSplitURL
  where seq = 7;

Code #3 is the outline of the code; you must add function of type split string returning tokens and the sequence of it in the string.

Articles containing functions split string:

• Tally OH! An Improved SQL 8K "CSV Splitter" Function
• Split strings the right way - or the next best way