Solved

Removing dates from within a text fields

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

785 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