March 24, 2009 at 5:33 am
Hi guys,
I've had a few of our dev guys try to resolve this so maximum points to anyone who can work it out...
I want to extract the dates from the following strings, and have been using regex to try and achieve it.
sdklfjsd 2008/09 lsdjkfsdjkl
2008/09 lsdjkfsdjkl
sdklfjsd 2008/09
456 jsd 2008/09 lsdjkfsdjkl
45 2008/09 lsdjkfsdjkl
The expression I have tried to match is: [0-9]{4}/[0-9]{2} and it works fine if I do a replace. So if I replaced it with a blank it would remove the valid dates from the above text.
What I actually want to do though is to remove all text except for the date. I thought that if I used:
^[0-9]{4}/[0-9]{2}
or
^[0-9]{4}/[0-9]{2}$
it would work but it doesn't.
Anyone shed any light??????
Cheers guys,
Joe
March 24, 2009 at 7:04 am
Can there be multiple dates in one string and do you want to preserve them all?
If not, then don't mess around with substitutions, just set the string to be equal to the value of the string matched by RegEx.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 24, 2009 at 9:49 am
No there will only be one date in each string.
How would I set the string to be equal the match? That's the bit I am struggling with.
Cheers,
Joe
March 24, 2009 at 10:16 am
Can you show us some of the code? If we want to talk brass tacks, then it would be easier to explain it in context than trying to explain it abstractly.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 24, 2009 at 10:39 am
Here's the script I am using, which strips out the dates I am actually wanting to retain...
select top 10 dbo.RegexReplace('[0-9][0-9][0-9][0-9]/[0-9][0-9]', '', description_short, 1, 1), description_short from ee_product
where description_short like '%[0-9][0-9][0-9][0-9]/[0-9][0-9]%'
Output...
My Football Shirt, My Football Shirt 2008/09
My Shirt, My 2008/09 Shirt
Shirt, 2008/09 Shirt
...
Ideally I want the following output...
2008/09, My Football Shirt 2008/09
2008/09, My 2008/09 Shirt
2008/09, 2008/09 Shirt
...
March 24, 2009 at 10:56 am
Joe Doherty (3/24/2009)
select top 10 dbo.RegexReplace('[0-9][0-9][0-9][0-9]/[0-9][0-9]', '', description_short, 1, 1), description_short from ee_productwhere description_short like '%[0-9][0-9][0-9][0-9]/[0-9][0-9]%'
OK, "dbo.RegexReplace()" is your CLR function that works like the SQL Replace() function, but with a RegEx pattern I assume?
Then with this pattern, you can just use SubString & PatIndex:
select top 10
Substring(description_short
, patindex('%[0-9][0-9][0-9][0-9]/[0-9][0-9]%',description_short)
, 7)
, description_short
from ee_product
where description_short like '%[0-9][0-9][0-9][0-9]/[0-9][0-9]%'
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 25, 2009 at 5:33 am
Genuis!
Thank you very much.
Joe
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply