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: Thursday, August 14, 2014 7:21 AM
Points: 7, Visits: 53
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: Yesterday @ 10:41 AM
Points: 1,377, Visits: 1,569
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: Thursday, August 14, 2014 7:21 AM
Points: 7, Visits: 53
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: Yesterday @ 10:41 AM
Points: 1,377, Visits: 1,569
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 @ 11:36 AM
Points: 3,545, Visits: 7,655
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.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

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: Thursday, August 14, 2014 7:21 AM
Points: 7, Visits: 53
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