question

Cornelmar78 avatar image
Cornelmar78 asked

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 serversql query
2 comments
10 |1200 characters needed characters left characters exceeded

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

  • 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 Likes 0 ·

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 Likes 0 ·

1 Answer

· Write an Answer
WRBI avatar image
WRBI answered
  1. 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!
  2. 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.
  3. 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 comment
10 |1200 characters needed characters left characters exceeded

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

SELECT

COLUMN

FROM [TABLE]

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

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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