Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Return value between two pipe symbols

Return value between two pipe symbols

Return value between two pipe symbols


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.


RE: Return value between two pipe symbols

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

---- Andy

There is a great need for a sarcasm font.

RE: Return value between two pipe symbols

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.

RE: Return value between two pipe symbols

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 

RE: Return value between two pipe symbols

Many thanks for that DaveInIowa. It worked perfectly thumbsup2

RE: Return value between two pipe symbols

So maybe you should give Dave a Star... ponder

---- Andy

There is a great need for a sarcasm font.

RE: Return value between two pipe symbols

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

RE: Return value between two pipe symbols

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:


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.


RE: Return value between two pipe symbols

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close