Cleansing data on SQL - String

Question

I am trying to clean my data on SQL. My sample data is below:

Could you please let me know how can I remove the date-time parts? Thanks,

https://docs.google.com/spreadsheets/d/1BvVxU0_a53vncGpd0Mbr57WPUbG89MgfpYGah0JBdIA/edit?usp=sharing

Where is the source of your data? Which language are you using? Are you inserting the cleaned data or selecting to display it? It may be easier to exclude unwanted data from the source. — Albert Alberto, Sep 06 '21 at 06:29
Is there even a thing called "Microsoft" SQL? I am afraid there is not. — Infinity, Sep 06 '21 at 06:35
https://docs.google.com/spreadsheets/d/1BvVxU0_a53vncGpd0Mbr57WPUbG89MgfpYGah0JBdIA/edit?usp=sharing — Simon Soltanian, Sep 06 '21 at 06:38
The document cannot be opened. Open the document on google drive the generate a shareable link then post it here. — Albert Alberto, Sep 06 '21 at 06:48
https://docs.google.com/spreadsheets/d/1BvVxU0_a53vncGpd0Mbr57WPUbG89MgfpYGah0JBdIA/edit?usp=sharing — Simon Soltanian, Sep 06 '21 at 06:54
Post any relevant information in the question itself. Explain what you want without forcing people to guess — Panagiotis Kanavos, Sep 06 '21 at 07:15
I want to clean the data in SQL. https://docs.google.com/spreadsheets/d/1BvVxU0_a53vncGpd0Mbr57WPUbG89MgfpYGah0JBdIA/edit?usp=sharing — Simon Soltanian, Sep 06 '21 at 07:16
If you want to remove the date string you'll have to do it with a language like C# or Python. SQL, the language, is very bad at text manipulation. It's a query language, not a general purpose language — Panagiotis Kanavos, Sep 06 '21 at 07:17

Dušan · Answer 1 · 2021-09-06T07:43:42.580

0

You can use this regex for selecting date-times that you want to remove:

(\(?[0-9]{2}\/){2}([0-9]{4}\)?)

Then replace that part of string with blank space, which is the same as removing.

To see how to use replace method in SQL with regex check this answer.

edited Sep 06 '21 at 07:43

answered Sep 06 '21 at 07:38

Dušan

269
1
11

score 0 · Accepted Answer · answered Sep 06 '21 at 12:16

SQL Server is not particularly good at string manipulations.

However, based on the examples in your question, the "cleaning" is either removing the first 11 characters or the last 13 characters, depending on where the date is. Fortunately, this is actually something you can do in SQL Server:

select t.*,
       (case when data like '% ([0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9])'
             then left(data, len(data) - 13)
             when data like '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9] %'
             then stuff(data, 1, 11, '')
             else data
        end) as cleaned_data
from t;

Cleansing data on SQL - String

2 Answers2