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
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
Exclude a Column Using Select * [Except Columna] from Tablea
Number of Times a Value Appears (Sql)
Split and Get Second Row as Value
Er_Access_Denied_Error: Access Denied for User ''@'Localhost' (Using Password: No)
Mysql Table Insert If Not Exist Otherwise Update
Compare 2 Column Values in Same Table
How to Use Select Distinct and Concat in the Same SQL Statement
Sql Max Function Returns One Row When Multiple Rows Have the Same Value
Difference Between "INNER JOIN" and "OUTER JOIN"
How to Check If More Than One Column Is Not Null Per Record
Mismatched Input 'From' Expecting <Eof> SQL
How to Calculate Percentage Between Two Numbers Using SQL on Bigquery
How to Convert from Blob to Text in MySQL
Combining (Concatenating) Date and Time into a Datetime
Simple Check for Select Query Empty Result