Get the Character Between First 2 Special Character in SQL

Get the character between first 2 special character in SQL

This would be my approach:

SELECT CAST('<x>' + REPLACE(data,'_','</x><x>') + '</x>' AS XML).value('/x[2]','int')
FROM YourTable

First you transform this to an XML and then you pick the second node..

EDIT: Some more examples where this approach is usefull:

CROSS APPLY: You can use this approach to get several tokens at once

DECLARE @tbl TABLE(separated VARCHAR(100));
INSERT INTO @tbl VALUES('1_23:50_Look_this_is_a_test'),('2_12:00_that''s_one_more_test'),('3_13:30_great!_It_works!');

SELECT Converted.value('/x[1]','int') AS number
      ,Converted.value('/x[2]','time') AS time
      ,Converted.value('/x[3]','varchar(max)') AS text
FROM @tbl
CROSS APPLY(SELECT CAST('<x>' + REPLACE(separated,'_','</x><x>') + '</x>' AS XML) AS Converted) AS MySeparated
--type-safe and easy:
/*
number  time    text
1       23:50   Look
2       12:00   that's
3       13:30   great!
*/
GO

CTE: use as parameter

DECLARE @Parameter VARCHAR(100)='1_12:30_SomeValue';
WITH MyParameters AS
(
    SELECT CAST('<x>' + REPLACE(@Parameter,'_','</x><x>') + '</x>' AS XML).value('/x[1]','int') AS IntParam
          ,CAST('<x>' + REPLACE(@Parameter,'_','</x><x>') + '</x>' AS XML).value('/x[2]','time') AS TimeParam
          ,CAST('<x>' + REPLACE(@Parameter,'_','</x><x>') + '</x>' AS XML).value('/x[3]','varchar(max)') AS TextParam
)
SELECT IntParam,TimeParam,TextParam
FROM MyParameters
/*
IntParam    TimeParam   TextParam
1           12:30:00    SomeValue
*/
GO

Split String: Transform to list

DECLARE @MyIDs VARCHAR(100)='3,5,7';
SELECT A.B.value('.','int') TheIntValue
FROM(SELECT CAST('<x>' + REPLACE(@MyIDs,',','</x><x>') + '</x>' AS XML) AS MyListAsXML) AS x
CROSS APPLY MyListAsXML.nodes('/x') AS A(B)

/*
TheIntValue
3
5
7
*/
GO

Dynamic IN Statement

DECLARE @tbl TABLE(ID INT,Content VARCHAR(max));
INSERT INTO @tbl VALUES(1,'Value 1'),(2,'Value 2'),(3,'Value 3'),(4,'Value 4'),(5,'Value 5'),(6,'Value 6'),(7,'Value 7');

DECLARE @MyIDs VARCHAR(100)='3,5,7';
/*
This won't work (due to the fact, that @MyIDs is not a list of INTs but a text
SELECT * FROM @tbl WHERE ID IN(@MyIDs)
*/
WITH AsList AS
(
    SELECT A.B.value('.','int') TheIntValue
    FROM(SELECT CAST('<x>' + REPLACE(@MyIDs,',','</x><x>') + '</x>' AS XML) AS MyListAsXML) AS x
    CROSS APPLY MyListAsXML.nodes('/x') AS A(B)

)
SELECT * FROM @tbl WHERE ID IN(SELECT TheIntValue FROM AsList)

/*
ID  Content
3   Value 3
5   Value 5
7   Value 7
*/

How to extract strings between two special different characters in TSQL

This will replace everything up to and including the first : using stuff. Then it uses that same result to find the first . and substring the stuff result up to the first .

DECLARE @c varchar(100)
SET     @c = 'Microsoft.SystemCenter.UserActionManager:ServerName_1.domain.net;ServerName_2.domain.net'

SELECT SUBSTRING(STUFF(@c, 1, CHARINDEX(':',@c), ''), 0, CHARINDEX('.', STUFF(@c, 1, CHARINDEX(':',@c), '')))

SQL: how to select a substring between special characters

Use substring, like this (only works for the specified pattern of two slashes, characters, then another slash):

declare @str varchar(100) = '\\abcde\cc\xxx'

select substring(@str, 3, charindex('\', @str, 3) - 3)

Replace @str with the column you actually want to search, of course.

The charindex returns the location of the first slash, starting from the 3rd character (i.e. skipping the first two slashes). Then the substring returns the part of your string starting from the 3rd character (again, skipping the first two slashes), and continuing until just before the next slash, as determined by charindex.

Edit: To make this work with different numbers of slashes at the beginning, use patindex with regex to find the first alphanumeric character, instead of hardcoding that it should be the third character. Example:

declare @str varchar(100) = '\\\1Abcde\cc\xxx'

select substring(@str, patindex('%[a-zA-Z0-9]%', @str), charindex('\', @str, patindex('%[a-zA-Z0-9]%', @str)) - patindex('%[a-zA-Z0-9]%', @str))

How to select a string between 2 identical characters in SQL

Another way to get the data you want it to use left() and right() functions.

select left(right(t, len(t)- CHARINDEX('"', t)), charindex('"',right(t, len(t)- CHARINDEX('"', t)))-1)
from
(
select 'Content-Disposition: attachment; filename="0001.zam"' t
) u

This outputs

0001.zam

I am hoping, rather than assuming, that there are only two " in this header.

How to extract strings between two special characters in TSQL

DECLARE @c varchar(100)
SET     @c = 'Email_Monday_Miami_June' 

SELECT SUBSTRING(
    @c, 
    CHARINDEX('_', @c) + 1, 
    LEN(@c) - CHARINDEX('_', @c) - CHARINDEX('_', REVERSE(@c))
)

returns

Monday_Miami

Extract character between the first two characters

If I follow you correctly, you can use split():

(split(col, '_'))[safe_ordinal(2)]

split() turns the string column to an array of values, given a separator (here, we use _). Then we can just grab second array element.

SQL retrieve string that have special character

So I did not test this, it may have typos, but the basic idea is to convert your text into XML and then use the XML tools to query the result. This is much easier than trying to parse a string with a dynamic number of lines.

First we extract the xml string from the table converting the whole thing to an xml string with rows for each line and use replace on the CR LF to make seperators:

SET @xmlstr  = 
  SELECT CAST('<file><row>' + REPLACE(COMMENT,CHAR(13)+CHAR(10),'</row><row>') + '</row></file>' AS XML)
  FROM PERMIT A
  INNER JOIN PROCESS G ON A.CODE = G.CODE 

Then select the lines you want from the XML

SELECT line.a_row
FROM    @xmlstr.nodes('/file') AS line(a_row)
WHERE right(line.a_row,1) = '+'

You can find many examples of using this trick (converting to xml to aid in parsing) on this website and around the web.

Find length of the string between two given special character

SELECT CHARINDEX(',', '341267-8763,68978') - CHARINDEX('-', '341267-8763,68978') - 1

4

---

Related Topics