Return value between two pipe symbols 3

[gk17]

Hi,

How can I extract values with a specific format from a field that has a lot of pipe "|" symbols?

For example:

Testing|some text here|AB-17-1234567|...|...|...

The value I want is AB-17-1234567 where the first two characters (which won't always be the same) will always be a letter and the rest after that will always be some numbers. I want to say it should be 2 numbers followed by 7 numbers consistently but is there a way to return that value and stop at the next pipe symbol? I only want those characters returned between the two pipes.

Using MS SQL Server Management Studio v11.x to do this.

Thanks.

 

[Andrzejek]

You could use STRING_SPLIT() function and look for the 2nd value which should give you [tt]AB-17-1234567[/tt]



---- Andy

There is a great need for a sarcasm font.

 

[gk17]

Thanks for the reply. That seems to return the value from a static location. The value I'm looking for may not be in the same location. I was trying to find a way to match the pattern (XX-##-#######) and return that value only. I found PATINDEX but don't know how to use it. I know it looks for the position but I need the value themselves. Also, not sure if I can match by hyphens since this field I'm searching on also has social security which also has two hyphens.

 

[DaveInIowa]

This is pretty ugly but might provide the results you're after...

SELECT SUBSTRING(TextColumn, PATINDEX('%[A-Z][A-Z]-[0-9]%|%', TextColumn), CHARINDEX('|', TextColumn,  PATINDEX('%[A-Z][A-Z]-[0-9]%|%', TextColumn)) - PATINDEX('%[A-Z][A-Z]-[0-9]%|%', TextColumn)) YourValue
  FROM YourTable

EDIT: This would be easier to read and maintain...

DECLARE @Pattern VARCHAR(32) = '%[A-Z][A-Z]-[0-9]%|%'

SELECT SUBSTRING(TextColumn, PATINDEX(@Pattern, TextColumn), CHARINDEX('|', TextColumn,  PATINDEX(@Pattern, TextColumn)) - PATINDEX(@Pattern, TextColumn)) YourValue
  FROM YourTable

 

[gk17]

Many thanks for that DaveInIowa. It worked perfectly

 

[Andrzejek]

So maybe you should give Dave a Star...


---- Andy

There is a great need for a sarcasm font.

 

[gk17]

Didn't know I can do that. Only come here occasionally for help. Consider it done. Star given

 

[gk17]

I need this query modified to pull a different set of data. How do I change it so it only looks between two pipes for 8 digits/numbers exact? Here's an example of a field containing all the pipe symbols and the value I want to extract:

|....|....|12345678|....|....

I want to pull 12345678. It can be any random number between 0-9 for those 8 digits.

I tried changing the pattern to:

DECLARE @Pattern VARCHAR(32) = '%|[0-9]|%'

That returned a blank value. I wanted to use LEN=8 but don't know how to apply it in this case.

Thanks.

 

[Olaf Doschke]

STRING_SPLIT() does split at a given character, not at a given position. And as you see from Dave's code functions working with positions are dynamic, because there also are functions, whi determine positions of some character or pattern.

Just look at:

Select * From String_Split('Testing|some text here|AB-17-1234567|...|...|...','|')

For a single string manipulation STRING_SPLIT() is less handy, as it doesn't result just in one string part, it results in a table with all parts. STRING_SPLIT() can be used to split a text line of CSV or also pipe delimited values into a column of one single value per record, that's it's main intention. The value between the first and second pipe symbol, therefore, is in row 2 of the STRING_SPLIT() result.

But if you want to process all the values anyway, you could first split up data and then go on processing it picking a specific row number (previously a specific column number).

Bye, Olaf.


Olaf Doschke Software Engineering

https://www.doschke.name