A SQL Query to Select a String Between Two Known Strings

T-SQL Extract string between two known strings

CHARINDEX has a third, optional argument, which is the start. Modify your query as follows to begin looking for --- after the first occurence.

select SUBSTRING(@Text, CHARINDEX('Original ----- ', @Text)
, CHARINDEX(' ----- ',@Text, CHARINDEX('Original ----- ', @Text) + len('Original ----- '))) + '-----';

A quick Fiddle to demonstrate.

SQL Query to select a value between two known strings

It is starting at the start of the tag not the end of it.

 SELECT cast(
   SUBSTRING(
     d_info, 
     CHARINDEX('<nettoeinkommen>', d_info) + len('<nettoeinkommen>'),
     CHARINDEX('</nettoeinkommen>', d_info) - (CHARINDEX('<nettoeinkommen>', d_info) + len('<nettoeinkommen>'))
   ) as float(8)) as income
 from dbo.Details

you might even have these defined in variables:

 SELECT cast(
   SUBSTRING(
     d_info, 
     CHARINDEX(@startTag, d_info) + len(@startTag),
     CHARINDEX(@endTag, d_info) - (CHARINDEX(@startTag,d_info)+ len(@startTag))
   ) as float(8)) as income
 from dbo.Details

I think the code is much easier to understand with the variables.

How to select a string between two strings in a column in SQL Server?

Here is a way to extract the text between two fixed strings. Not exactly sure what you were doing with the @Text variable so I used it as an example below.

DECLARE @Text NVARCHAR(MAX) = 'blah blah Manufacturer Name : MY NAME Manufacturer Part blah blah'
SELECT SUBSTRING(@Text, CHARINDEX('Manufacturer Name : ', @Text) + LEN('Manufacturer Name : ') + 1, CHARINDEX('Manufacturer Part',@Text) - (CHARINDEX('Manufacturer Name : ', @Text) + 2 + LEN('Manufacturer Name : ')) )

SELECT SUBSTRING(Description, CHARINDEX('Manufacturer Name : ', Description) + LEN('Manufacturer Name : ') + 1, CHARINDEX('Manufacturer Part',Description) - (CHARINDEX('Manufacturer Name : ', Description) + 2 + LEN('Manufacturer Name : ')) )
FROM tbIMPACArchiveNew 
WHERE Description LIKE '%Manufacturer Name : %Manufacturer Part'

Extract string between two characters in a string

For this dataset, string functions should do it:

select blob_nm, substring(blob_nm, len(blob_nm) - 17, 14) res from #temp

The idea is to count backwards from the end of the string, and capture the 14 characters that preced the extension (represented by the last 4 characters of the string).

Demo on DB Fiddle:

blob_nm                                        | res           
:--------------------------------------------- | :-------------
products_country_20200528102030.txt            | 20200528102030
products_territory_20190528102030.txt          | 20190528102030
products_country_2020-05-20_20200528102030.txt | 20200528102030

Select a string between two known strings and output greater than one

Do you mean something like:

DECLARE @c varchar(100)

set @c = 'Leslie|N|McCrory'

SELECT SUBSTRING(STUFF(@c, 1, CHARINDEX('|',@c), ''), 0, CHARINDEX('|', STUFF(@c, 1, CHARINDEX('|',@c), '')))
WHERE LEN(SUBSTRING(STUFF(@c, 1, CHARINDEX('|',@c), ''), 0, CHARINDEX('|', STUFF(@c, 1, CHARINDEX('|',@c), '')))) > 1

sql Return string between two characters

You can try something like this.

When there is no dash, it starts at the space if there is one or take the whole string if not.
Then I look if there is only one dash or 2

declare @string varchar(100) = 'BLAH90-ExtractThis-WOW'
declare @dash_pos integer = CHARINDEX('-',@string)

SELECT CASE 
WHEN @dash_pos = 0 THEN 
       RIGHT(@string,LEN(@string)-CHARINDEX(' ',@string))
ELSE (
    CASE 
        WHEN @dash_pos = LEN(@string)-CHARINDEX('-',REVERSE(@string))+1 
        THEN RIGHT(@string,LEN(@string)-@dash_pos)
    ELSE SUBSTRING(@string,@dash_pos+1, CHARINDEX('-',@string,@dash_pos+1) - 
                                                     @dash_pos -1) 
    END
) 
END as My_String  

---

Related Topics