SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to return only certain text out of a column
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

JR83
Starting Member

South Africa
31 Posts

Posted - 05/15/2013 :  09:51:59  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 05/15/2013 :  09:57:23  Show Profile  Reply with Quote
Might search for word "Subject:"

--
Chandu
Go to Top of Page

JR83
Starting Member

South Africa
31 Posts

Posted - 05/15/2013 :  10:01:32  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/16/2013 :  00:17:54  Show Profile  Reply with Quote
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
Go to Top of Page

JR83
Starting Member

South Africa
31 Posts

Posted - 05/17/2013 :  01:28:16  Show Profile  Reply with Quote
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
Go to Top of Page

JR83
Starting Member

South Africa
31 Posts

Posted - 05/17/2013 :  01:41:47  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/17/2013 :  01:43:49  Show Profile  Reply with Quote

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

JR83
Starting Member

South Africa
31 Posts

Posted - 05/17/2013 :  01:48:42  Show Profile  Reply with Quote
Thanks :)
but now i get this error: Argument data type text is invalid for argument 1 of stuff function.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/17/2013 :  01:57:25  Show Profile  Reply with Quote
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

Edited by - visakh16 on 05/17/2013 01:57:41
Go to Top of Page

JR83
Starting Member

South Africa
31 Posts

Posted - 05/17/2013 :  02:09:41  Show Profile  Reply with Quote
I have changed it to varchar(max), but i am still getting all the text and not just the words subject: Birthday
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/17/2013 :  02:36:49  Show Profile  Reply with Quote
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
Go to Top of Page

JR83
Starting Member

South Africa
31 Posts

Posted - 05/17/2013 :  02:51:50  Show Profile  Reply with Quote
Thank you for your help

Much appreciated :)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/17/2013 :  02:53:38  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000