Solved

Removing dates from within a text fields

Posted on 2013-06-12
5
209 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 41

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 25

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 25

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

679 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