Get rid off right and left side

[RaulMA]

The following is an output of a single field
select message from table a

test CSFB, ABN, C, BSC, BNPP, LEH, MER, BARX, BAS, FTN requests


I need a command just to get the list between test and request.
Any help will be appreciated.

Raul

 

[SQLDenis]

One way

declare @v varchar(666)
select @v ='test CSFB, ABN, C, BSC, BNPP, LEH, MER, BARX, BAS, FTN requests'

select ltrim(replace(replace(@v,'test',''),'requests',''))



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLSister]

Look at substring and patindex and charindex and right. Basically you want to identify the location of the first space and use it to define the start of the substring and then find the locatiopn of the last space (using right to search from the end to the first space) and use that to define where to stop the substring.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[gmmastros]

Select SubString(Message, 6, len(Message) - 14)
From   Table

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLSister]

Note that dennis's way is simpler but will only work if the exact text is always there. If the first and last chunk of text will vary then you need to use my method.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[RaulMA]

Some comments
declare a parameter like
@v ='test CSFB, ABN, C, BSC, BNPP, LEH, MER, BARX, BAS, FTN requests'
it won't work in my case because the list between test and requests could be different.

I need to identify when I found test and get rid off all I have on the left including test. I also need to search for requests and get rid off all I have to the right including requests. The final out put should be like this (not always the case)
CSFB, ABN, C, BSC, BNPP, LEH, MER, BARX, BAS, FTN


Thanks

 

[SQLDenis]

I just declared it so that you could run the example

In your case it would be
SELECT select ltrim(replace(replace(YourColumn,'test',''),'requests',''))
FROM YourTable

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[RaulMA]

SQL Denis

I think I did not explain clearly
test CSFB, ABN, C, BSC, BNPP, LEH, MER, BARX, BAS, FTN requests

That is jus a sample your query get rid off test and request as expected but what happens if my query returns

blah blah test CSFB, ABN, C, BSC, BNPP, LEH, MER, BARX, BAS, FTN requests hola denis

I test your query and it just get rid off test and requests I got an output like this

blah blah CSFB, ABN, C, BSC, BNPP, LEH, MER, BARX, BAS, FTN hola denis

Any suggestions?

 

[SQLDenis]

declare @v varchar(666)
Declare @searchstring1 varchar(50)
Declare @searchstring2 varchar(50)

select @searchstring1 ='%test%'
select @searchstring2 ='%requests%'
select @v='blah blah test CSFB, ABN, C, BSC, BNPP, LEH, MER, BARX, BAS, FTN requests hola denis'

select
left(right(@v,len(@v)-(patindex(@searchstring1,@v)+ len(@searchstring1) -2)),
len(right(@v,len(@v)-(patindex(@searchstring1,@v)+ len(@searchstring1) -2)))
- (len(@searchstring2) -2 +(patindex(reverse(@searchstring2),reverse(@v)))))


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/