Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Substring / Patindex for wildcard select Expand / Collapse
Author
Message
Posted Friday, January 17, 2014 9:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 5:29 AM
Points: 7, Visits: 44
Hi,

I am trying to extract a date string from a field, if the field populated has CED: before it.

So if the comment field shows CED:01/01/14 then give me 01/01/14

There could be characters prior or after the CED:xx/xx/xx

select
SUBSTRING (comment,PATINDEX('%CED:%',comment)+4,LEN(Comment)) as Date
from data

It's pulling back data even though CED: doesn't exist in the Comment field for some reason. I am getting the date in places so it's part working.

I cannot use a where as this is an add on to a current script.

Thanks for your help. This is doing my head in!!!!

Thanks,

Gwyn
Post #1532117
Posted Friday, January 17, 2014 9:14 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 6:09 AM
Points: 1,342, Visits: 1,512
If you want a result for every row, then use a case statement to make the result as you wish when "CED:" is present.
If you only want results for rows that do contain "CED:", then use a where clause to limit.
Post #1532122
Posted Friday, January 17, 2014 9:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 5:29 AM
Points: 7, Visits: 44
Thanks

I will try that case. Cannot use where as script is huge as it is with lots of unions and where clauses so just wanted a nice easy add on in the select!!

Why is it giving output to fields that don't contain CED?!
Post #1532125
Posted Friday, January 17, 2014 9:19 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 6:09 AM
Points: 1,342, Visits: 1,512
Because there is nothing that tells it not to :)
Post #1532127
Posted Friday, January 17, 2014 9:27 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:51 PM
Points: 3,353, Visits: 7,247
gwyn.jones (1/17/2014)
Thanks

I will try that case. Cannot use where as script is huge as it is with lots of unions and where clauses so just wanted a nice easy add on in the select!!

Why is it giving output to fields that don't contain CED?!


Because PATINDEX('%CED:%',comment) will return 0 if the field doesn't contain CED. With the CASE statement, you can't validate to use values only when PATINDEX('%CED:%',comment) > 0



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1532132
Posted Friday, January 17, 2014 9:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 5:29 AM
Points: 7, Visits: 44
Thanks for the replies - I get it now!

Did the case, then a right() to get the date.

Cheers for the help
Post #1532141
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse