CODEWITHSUNDEEP
sql
jstyra
jstyra

Reputation: 1

Removing bad data from a field but leaving the good data

I have an issue where is bad data in a column on a table. The good data is always an unspecified number of integers separated by a / and then two characters. The problem is that i have any number of periods, ....., after the two characters.

Examples: 1/JP... or 25/US.. of 100/GB. etc....

What delete statement would remove the periods (.) but leave everything before the two characters?

Upvotes: 0

Views: 97

Answers (2)

mkRabbani
mkRabbani

Reputation: 16908

You can also think about SUBSTRING method using CHARINDEX as below-

DECLARE @Str VARCHAR(100) = '1/JP...'
SELECT SUBSTRING(@Str,0,CHARINDEX('/',@Str,0)+3)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270391

I am assuming that the two characters after the / are not periods.

If you just want to remove periods, then you can use replace():

select replace(col, '.', '')

If you want to change the data so there are no periods, then you can use update:

update t
    set col = replace(col, '.', '')
    where col like '%/%.';

Upvotes: 2

Related Questions