Find a numeric group in the string

2

Hello, I'd like some help finding 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 127 to 147 that are my group " 00001138271467122784", but the string size on the other lines is varied, my number can start before or after, the start and end order is not fixed. I have '_' to be able to delimit, but how? SQL Server Management Studio

    
asked by anonymous 18.05.2017 / 22:34

1 answer

2

Here's a demonstration of using the charindex function to find the snippet you're 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 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;

UPDATE

  

I 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 of the Ninth '_' and between them is the field I want.

Considering the additional explanations about the "_" separator, you can create a specific function to get 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 looks like this:

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

Another option is to use split string to divide 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 code sketch; you must add a function of type split string that returns tokens and its string in string .

Articles containing split string functions:

18.05.2017 / 23:04