Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Removing dates from within a text fields

Posted on 2013-06-12
5
Medium Priority
?
217 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

721 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