T-Sql Query to Get the String Between Two Special Characters

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), '')))

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

Find length of the string between two given special character

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

4

A SQL Query to select a string between two known strings

The problem is that the second part of your substring argument is including the first index.
You need to subtract the first index from your second index to make this work.

SELECT SUBSTRING(@Text, CHARINDEX('the dog', @Text)
, CHARINDEX('immediately',@text) - CHARINDEX('the dog', @Text) + Len('immediately'))

T-SQL Extract string between two known characters

Just another option

Example

Declare @YourTable Table ([Car] varchar(50))
Insert Into @YourTable Values
('ORANGE 1 FORD FIESTA;PETROL')
,('RED 24 FORD MUSTANG; PETROL')
,('BLACK 2 NISSAN JUKE; DIESEL')

Select A.*
,NewValue = stuff(left(Car,charindex(';',Car+';')-1),1,patindex('%[0-9] %',Car+'0'),'')
From @YourTable A

Returns

Car                            NewValue
ORANGE 1 FORD FIESTA;PETROL FORD FIESTA
RED 24 FORD MUSTANG; PETROL FORD MUSTANG
BLACK 2 NISSAN JUKE; DIESEL NISSAN JUKE

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.

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))

SQL query to get specific string between characters

You can use SPLIT_STRING to break up the string to individual values and then parse out and identify the values you need using a case statement and finally then pivot the rows to columns so its easy to insert each specific value.

select
date, [file], size
from (
select
case
when value like '%/%/%' then 'date'
when ISNUMERIC(value) = 0 and value like '%.%' then 'file'
when ISNUMERIC(value) = 1 and value like '%.%' then 'size'
else 'na' end type,value
from (select value from STRING_SPLIT ( '15/07/2020 14:31 1.395 image1.jpeg' , ' ' ) ) d
) src
pivot
(
max(value)
for type in ([date], [file], [size])
) as piv

Sample Image

https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15
https://docs.microsoft.com/en-us/sql/t-sql/functions/isnumeric-transact-sql?view=sql-server-ver15
https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15



Related Topics



Leave a reply



Submit