Extract Part of String - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Extract Part of String

Author

Topic

sz1
Aged Yak Warrior

555 Posts

Posted - 2011-03-22 : 09:21:29

Help please,

I need to extract strings from big description fields that are = to 'DNS' and 'MS'. I know I can pull this with IN, LIKE %DNS%...statements but I only want to pull say 2 lines that includes the text I want and not the whole description field.

Can anyone put me on the right track here?

Thanks
sz

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-22 : 09:24:39

what do you mean by "2 lines that includes the text" ? You only wanted 2 records from the table ?

Post some sample data and show us your required result

KH
[spoiler]Time is always against us[/spoiler]

latch
Yak Posting Veteran

62 Posts

Posted - 2011-03-22 : 09:30:05

Hi,

if you want to retrieve two lines from the table as khtan said:

you can use TOP clause as :

SELECT TOP 2 column's' from table where <condition> LIKE

We need sample data to guide you better.

Thanks,
latch

sz1
Aged Yak Warrior

555 Posts

Posted - 2011-03-22 : 09:39:36

Say I want to extract the words DNS from description but there are 10 lines and the word 'DNS' is on line 7, of course this could be any line, then I want to extract the text with say 5 words before the word 'DNS and 5 after the word 'DNS.

SELECT * FROM MyTable
WHERE Description LIKE '%DNS%'

--how do I then make the query only return part of the description field so the word 'DNS' is returned with only a small parrt of the whole text field...

Thanks
sz

Would RTRIM and LTRIM remove unwanted text? or is this only for blank values?

SELECT TRIM(' DNS ') AS 'MyDNSText' ---would this get rid of text or only spaces?

sz1
Aged Yak Warrior

555 Posts

Posted - 2011-03-22 : 09:53:40

quote:
Originally posted by sz1

Say I want to extract the words DNS from description but there are 10 lines and the word 'DNS' is on line 7, of course this could be any line, then I want to extract the text with say 5 words before the word 'DNS and 5 after the word 'DNS.

SELECT * FROM MyTable
WHERE Description LIKE '%DNS%'

--how do I then make the query only return part of the description field so the word 'DNS' is returned with only a small parrt of the whole text field...

Thanks
sz

Would RTRIM and LTRIM remove unwanted text? or is this only for blank values?

SELECT TRIM(' DNS ') AS 'MyDNSText' ---would this get rid of text or only spaces?

Something like this...

SELECT TRIM(TRAILING 'a' FROM 'aaabcdaaa')
==> aaabcd
SELECT TRIM(LEADING 'a' FROM 'aaabcdaaa')
==> bcdaaa

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-03-22 : 10:24:16

No such thing as TRIM()

Use LTRIM() and RTRIM()

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-03-22 : 10:32:29

quote:
Originally posted by sz1

Say I want to extract the words DNS from description but there are 10 lines and the word 'DNS' is on line 7, of course this could be any line, then I want to extract the text with say 5 words before the word 'DNS and 5 after the word 'DNS.

SELECT * FROM MyTable
WHERE Description LIKE '%DNS%'

--how do I then make the query only return part of the description field so the word 'DNS' is returned with only a small parrt of the whole text field...

Thanks
sz

Would RTRIM and LTRIM remove unwanted text? or is this only for blank values?

SELECT TRIM(' DNS ') AS 'MyDNSText' ---would this get rid of text or only spaces?

Post some sample data with expected result for this

Madhivanan

Failing to plan is Planning to fail

latch
Yak Posting Veteran

62 Posts

Posted - 2011-03-22 : 10:34:54

Hi,

you can try these:

select substring(column,PATINDEX ( '%_____DNS%',column) , PATINDEX ( '%DNS____%' , column ) ) from table

PATINDEX ( '%_____DNS%',column)----5 letters before DNS
PATINDEX ( '%DNS____%',column) --- 4 letters after DNS

Thanks,
latch

An TRIM is a function to remove white spaces in the sentence:
for eg:

select TRIM(' Good ') will output:'Good'
select LTRIM(' Good ') will be:'Good '
select RTRIM(' Good ') will be:' Good'

latch
Yak Posting Veteran

62 Posts

Posted - 2011-03-22 : 10:38:29

There is no such TRIM function available in SQL but you can use:

LTRIM(RTRIM(' Good ')

sz1
Aged Yak Warrior

555 Posts

Posted - 2011-03-22 : 12:24:15

Hi

I'm assuming the underscore is the amount of spaces...not to be included...?

Thanks
sz

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-03-22 : 12:48:23

quote:
Originally posted by sz1

Say I want to extract the words DNS from description but there are 10 lines and the word 'DNS' is on line 7, of course this could be any line, then I want to extract the text with say 5 words before the word 'DNS and 5 after the word 'DNS.

SELECT * FROM MyTable
WHERE Description LIKE '%DNS%'

--how do I then make the query only return part of the description field so the word 'DNS' is returned with only a small parrt of the whole text field...

Thanks
sz

Would RTRIM and LTRIM remove unwanted text? or is this only for blank values?

SELECT TRIM(' DNS ') AS 'MyDNSText' ---would this get rid of text or only spaces?

Perhaps it would be better if you come up with/post a sample of data laying in the Description column. And the desired output for that specific data.

Cheers
MIK

sz1
Aged Yak Warrior

555 Posts

Posted - 2011-03-22 : 12:54:22

Hi

Will post this when done.
Thanks
sz

X002548
Not Just a Number

15586 Posts

Posted - 2011-03-22 : 13:22:53

quote:
Originally posted by sz1

Hi

Will post this when done.
Thanks
sz

Well that's not gonna help us, help you

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources