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

Question

I want to get the string between two strings in a column of a table. How can I select the column? I have written the strings which are on either side. Can anyone help me how to include the column name with the string here?

DECLARE @Text NVARCHAR(MAX)

SELECT 
    SUBSTRING(@Text, CHARINDEX('Manufacturer Name : ', @Text), 
                     CHARINDEX('Manufacturer Part',@text) - CHARINDEX('Manufacturer Name : ', @Text) + LEN('Manufacturer Part')) 
FROM 
    tbIMPACArchiveNew 
WHERE 
    (Description LIKE '%Manufacturer Name: %' 
     OR Description LIKE '%Manufacturer Name : %') 
    AND Description LIKE '%Manufacturer Part%'

Expected result:

Column A                                                       Expected result
Manufacturer Name : ABC Manufacturer Part Number : XVB-C2B4         ABC
Manufacturer Name : DEF Manufacturer Part Number : 3RH1924-1GP11    DEF
Manufacturer Name : ABJ Manufacturer Part Number : FLDP-IOM248-0001 ABJ
Manufacturer Name : HIJ Manufacturer Part Number : L12/5MLLD0035    HIJ
Manufacturer Name : abhkdk Manufacturer Part Number : PEH1083510    abhkdk
Manufacturer Name : 1245 PUMP Manufacturer Part Number : 02-1010-55 1245

Manufacturer Name : ABC Manufacturer Part Number : XVB-C2B4 so this is all part of single column? — AVK
– AVK, Commented Aug 10, 2017 at 16:20

AVK · Accepted Answer · 2017-08-10 16:32:13Z

3

For a sample data of below

declare @table table (id int identity(1,1), data varchar(1000), descr varchar(1000))

insert into @table values ('Manufacturer Name : Manufacturer 1 Manufacturer Part : asjdfj','First Manufacturer')
insert into @table values ('Manufacturer Name : Manufacturer 2 Manufacturer Part : asjsadfasdfdfj','Second Manufacturer')
insert into @table values ('Manufacturer Name : Manufacturer 3 Manufacturer Part : er6ty','Third Manufacturer')

you can use

select substring(data, 
                 charindex('Manufacturer Name : ', data) + len('Manufacturer Name : '),
                 charindex('Manufacturer Part : ',data) - len('Manufacturer Part :') - 2) as Manufacturer_Name
       , descr
from @table

If you notice I am removing 2 characters at the end of substring to eliminate 'M' from Manufacturer Part and the Space before the actual text. You can use RTRIM but i chose this way.

answered Aug 10, 2017 at 16:32

AVK

3,9231 gold badge24 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Ali Faris Over a year ago

I think you could repalce charindex('Manufacturer Name : ', data) + len('Manufacturer Name : ') with 20 , good answer

AVK Over a year ago

@ali The reason why i did not do that is because what if data data not start with Manufacturer Name? If the data is always consistent then your suggestion is good.

Madhukar Over a year ago

But when I modify the code and execute, I'm getting below error. Msg 537, Level 16, State 5, Line 1 Invalid length parameter passed to the LEFT or SUBSTRING function.

AVK Over a year ago

@Madhukar this is a working example. What did you modify? can you show your modified code? update the question?

Madhukar Over a year ago

Modified code: select substring(Description, charindex('Manufacturer Name : ', Description) + len('Manufacturer Name : '), charindex('Manufacturer Part : ',Description) - len('Manufacturer Part :') - 2) as Manufacturer_Name , Description from tbIMPACArchieveNew where Description like '%Manufacturer name%'

JoeMc · Accepted Answer · 2017-08-10 16:34:05Z

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'

Ali Faris · Accepted Answer · 2017-08-10 16:31:46Z

0

I think this what you want

SELECT SUBSTRING ( Description , 20 , CHARINDEX("Manufacturer Part") - 20)
FROM  tbIMPACArchiveNew;

where 20 is the length of "Manufacturer Name : "

edited Aug 10, 2017 at 16:31

answered Aug 10, 2017 at 16:24

Ali Faris

18.7k12 gold badges51 silver badges75 bronze badges

Comments

Rams · Accepted Answer · 2017-08-10 16:33:14Z

0

You can get the index of 4th space and remove the first 20.

SELECT SUBSTRING ( Description , 20 , instr(Description,' ',1,4 ) - 20)
FROM  tbIMPACArchiveNew;

answered Aug 10, 2017 at 16:33

Rams

2,1591 gold badge15 silver badges20 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

5 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related