CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
GeoVIP
GeoVIP

Reputation: 1564

How to select data between two characters

Want select data from string which always start with * symbol and end with * OR |. For example, it must work for both strings: 232323*123456*123512 and 232323*123456|123512.

I try to parse like this : 

declare @s varchar(max) = '232323*123456*123512'
select parsename(replace(@s, '*', '.'), 2)

But this one works only with 232323*123456*123512. Need parser which will work with '232323*123456|123512' also. Response must be for both strings : 123456.

Upvotes: 2

Views: 224

Answers (3)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

One way of doing this is:

declare @s varchar(max) = '232323*123456|123512'

First replace all chars in order to have the same delimiters:

SET @s = REPLACE(@s, '|', '*')

Then select sub-string between first occurence of * and next occurence of *

SELECT SUBSTRING(@s, CHARINDEX('*', @s) + 1, CHARINDEX('*', @s, CHARINDEX('*', @s) + 1) - CHARINDEX('*', @s) - 1)

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93724

Use another Replace on top of your existing replace to replace both the symbols to '.'

DECLARE @S VARCHAR(MAX) = '232323*123456|123512'

SELECT Parsename(Replace(Replace(@S, '*', '.'), '|', '.'), 2) --123456

Update: to handle . replace the . with any other symbol do the parsing then finally replace the symbol with .

SELECT replace(Parsename(Replace(Replace(replace(@S,'.','~'), '*', '.'), '|', '.'), 2),'~','.') --12.34.56

Upvotes: 1

Keats_
Keats_

Reputation: 11

You can try replacing both '*' and '|':

declare @s varchar(max) = '232323*123456|123512'
select parsename(replace(replace(@s, '|', '.'), '*', '.'), 2)

Upvotes: 1

Related Questions