How to return only certain text out of a column

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

General SQL Server Forums

New to SQL Server Programming

How to return only certain text out of a column

Author

Topic

JR83
Starting Member

31 Posts

Posted - 2013-05-15 : 09:51:59

Hi all

I am trying to retrieve only certain text out of one column in order to build a report.

Table is report
field name is Description

text i want to retrieve is for example "Subject: Reference", i only want to retrieve the part where "subject" is displayed.

At the moment my code brings every line that has the word subject in which is incorrect i only require the subject line, as in like an email for example

Please help!

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-05-15 : 09:57:23

Might search for word "Subject:"

--
Chandu

JR83
Starting Member

31 Posts

Posted - 2013-05-15 : 10:01:32

I have searched for word "subject:" but it returns all text with the word subject in it. I want the part that has the subject line only not all the other text that goes with it

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-16 : 00:17:54

quote:
Originally posted by JR83

I have searched for word "subject:" but it returns all text with the word subject in it. I want the part that has the subject line only not all the other text that goes with it

when you serach for subject: it will only return you cases where you've subject: pattern coming. I'm not sure whether thats what you refer by subject line. If not, illustrate with an example what you mean by subject line by giving a string value and then show us what you want from it as output

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

JR83
Starting Member

31 Posts

Posted - 2013-05-17 : 01:28:16

my Example:
select Description from report where charindex('subject:',TextCol) > 0

This returns all rows with a word Subject in it, all text!

Like this: number: 33 subject: Birthday Company: Test company
----------------------------------------------------------------
I am looking for result: Subject: Birthday

JR83
Starting Member

31 Posts

Posted - 2013-05-17 : 01:41:47

sorry my mistake:

my Example:
select Description from report where charindex('subject:',description) > 0

This returns all rows with a word Subject in it, all text!

Like this: number: 33 subject: Birthday Company: Test company
----------------------------------------------------------------
I am looking for result: Subject: Birthday

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-17 : 01:43:49

[code]
select LEFT(STUFF(Description,1,patindex('%subject:%',TextCol)-1,''),CHARINDEX(' ',Description,patindex('%subject:%',TextCol)+8))
from report where charindex('subject:',TextCol) > 0
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

JR83
Starting Member

31 Posts

Posted - 2013-05-17 : 01:48:42

Thanks :)
but now i get this error: Argument data type text is invalid for argument 1 of stuff function.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-17 : 01:57:25

why are you using text as datatype? Its deprecated so you should be using varchar(max) instead


select LEFT(STUFF(Description,1,patindex('%subject:%',Description)-1,''),CHARINDEX(' ',Description,patindex('%subject:%',Description)+8))
from (Select CAST(Description AS varchar(max)) AS Description
from report)t where charindex('subject:',Description) > 0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

JR83
Starting Member

31 Posts

Posted - 2013-05-17 : 02:09:41

I have changed it to varchar(max), but i am still getting all the text and not just the words subject: Birthday

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-17 : 02:36:49

see full illustration below


declare @test table
(
description text
)
insert @test
select 'number: 33 subject: Birthday Company: Test company' union all
select 'test:123 number: 33 subject: Thanks Company: Test company'

select
LEFT(STUFF(Description,1,patindex('%subject:%',Description)-1,''),CHARINDEX(' ',STUFF(Description,1,patindex('%subject:%',Description)-1,''),patindex('%subject:%',STUFF(Description,1,patindex('%subject:%',Description)-1,''))+9))
from (Select CAST(Description AS varchar(max)) AS Description
from @test)t where charindex('subject:',Description) > 0



output
------------------------
subject: Birthday 
subject: Thanks

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

JR83
Starting Member

31 Posts

Posted - 2013-05-17 : 02:51:50

Thank you for your help

Much appreciated :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-17 : 02:53:38

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources