How can I remove alpabetic, specific characters values in a column to only return the numeric or desimal value. Values such as '11'.5', '12.y',13E.5','11.L9'

Question

question

Cornelmar78 asked Jun 25, '18

How can I remove alpabetic, specific characters values in a column to only return the numeric or desimal value. Values such as '11'.5', '12.y',13E.5','11.L9'

17.7.
15.1`
12.;9
13.R
12.`
11.I
13.I
12.2Q
14.4.
11`.5
14.4.
11.I8
11.I
12.Y
13.4.
13E.5
11.L9
13.1`
12.Y
12.1`
11.J4
12;.1
16.I
12W.5
10.9I
13.4.
11.1Y
14.1`
11.I
11.Y
11.T1
13.L4
11.2.
14.I
11.8.
11.6\
14/.1
11.4Y
12.2.
11.L6
11.5'
14.4.
12.8]
11;.9
11.7.
13./4
12.Y
12.I
13./4
13.I
11.I
12./3
12.Y
11.8'
11.I
11.I9
13.9'
11.L6

sql server sql query

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WRBI commented · Jun 25, 2018 at 01:39 PM

Is this dirty data that has already gotten into your tables or are you looking to cleanse the data before putting it into your tables?
Do you have access to SSIS to do ETL?
Do you have a way of stopping bad input in the source system?

0 ·

Cornelmar78 commented · Jun 26, 2018 at 05:35 AM

Hi, This is dirty data that got into the table and need to clean and write a code in order to prevent future dirty data like this to come through. I do have access to SSIS. What code can be used to clean the data and prevent data like this coming through. Any advice in stopping future bad input in the source system. Thank you.

0 ·

Answer 1 · 2018-06-26T07:47:02Z

WRBI answered Jun 26, '18

To stop dirty data getting into the application you'll probably want to speak with you application developers. They can employee various methods including drop down lists, text boxes that have masked input or implementing some front end logic that checks the users input etc. There are plenty of ways!
It's good that you have SSIS, data cleansing is a fairly large subject for this post: Cleaning Up Dirty Data with SSIS by Tim Mitchell. Is a good presentation that was done at SQLBits 11. That should get you started down the correct. If you have specific questions about Data Cleansing, open a new question and we'll help.
If it's just the above rows that you've shown that need cleaning up, backup your data and just use the REPLACE() function i.e REPLACE(YourColumnName, '.Y', '') - MSDN link to REPLACE()

Also, rather than posting answers for your reply, please use the 'Add Comment' button. Thank you.

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Cornelmar78 commented · Jun 26, 2018 at 06:31 AM

SELECT

COLUMN

FROM [TABLE]

WHERE (COLUMN LIKE'%__.._%' OR COLUMN LIKE'%__._.%'OR PATINDEX('%[^0-9.]%', COLUMN)> 0) AND COLUMN NOTIN('HHH','LLL')

0 ·

question