Solved

Removing dates from within a text fields

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

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.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
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…
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

739 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