Solved

Removing dates from within a text fields

Posted on 2013-06-12
5
206 Views
Last Modified: 2013-06-12
I have a text field with free entry text in it.  Within this text it may have something like:

;my it was such a great day on 4/4/2011, I wish all days were like this...

The formats of the dates vary, 04/04/2011 instead of 4/4/2011, the dates themselves also vary.

I also have things like 1/2 the day was great, and I need to keep the 1/2 and not confuse it with a date.  

I am open to doing this in something other than tsql.
0
Comment
Question by:kerryhill
  • 2
  • 2
5 Comments
 
LVL 40

Expert Comment

by:Sharath
ID: 39243108
Do you want to remove the dates completely and get the result as

";my it was such a great day on , I wish all days were like this..."

Do you always have "/" as separator in the date format or any other separator (for example hyphen) also?
0
 
LVL 24

Accepted Solution

by:
chaau earned 500 total points
ID: 39243190
You will use PATINDEX for this. It can be very easy done.

UPDATE Table1 SET
col1 = CASE WHEN PATINDEX('%__/__/____%', col1) > 0 THEN STUFF(col1, PATINDEX('%__/__/____%', col1), 10, '')
WHEN PATINDEX('%_/__/____%', col1) > 0 THEN STUFF(col1, PATINDEX('%_/__/____%', col1), 9, '')
WHEN PATINDEX('%__/_/____%', col1) > 0 THEN STUFF(col1, PATINDEX('%__/_/____%', col1), 9, '')
WHEN PATINDEX('%_/_/____%', col1) > 0 THEN STUFF(col1, PATINDEX('%_/_/____%', col1), 8, '')
ELSE col1 END

Open in new window


You can continue to add the patterns until all your data is corrected. Do not forget to adjust the third parameter in the STUFF function to the correct length of the pattern.

This is a single query approach. Of course the same thing can be done using WHILE loop with a list of patterns. Just let me know if you wish to explore this path, I will guide you
0
 

Author Comment

by:kerryhill
ID: 39243248
chaau, I will test this out.  I actually started doing something similar with PATINDEX, but I used PATINDEX('%[0-9]/%[0-9]/%[0-9]%', col1) > 0.  I just couldn't come up with the way to automatically fix it though.  I went this way because sometimes there were 1,2 or up to 4 characters for year.

Give me a bit and I will test this.
0
 

Author Closing Comment

by:kerryhill
ID: 39243254
This works quite nicely, thank you.  I like the single query approach.
0
 
LVL 24

Expert Comment

by:chaau
ID: 39243258
you can use [0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9] in place of __/__/____. This will match 01/07/2013. To match 1/07/2013 you need to use [0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]. I just gave you an idea. The actual CASE statement will need to be adjusted for each particular case.

E.g. you would need to include dash. In this case / needs to be replaced with [/-]
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now