CODEWITHSUNDEEP
sql-server
Camila
Camila

Reputation: 1

SQL Server Convert Data

I'm having trouble trying to clean a database because SQL Server doesn't differentiate '2¹59' from '2159', but when when try to convert into INT it obviously returns an error.

In this case I need to replace by NULL, every non numerical data.

Can someone help please? (I'm using Sql Server 2008)

Upvotes: 0

Views: 88

Answers (2)

Luaan
Luaan

Reputation: 63722

You can use a different collation to change the way the strings are compared:

select
  case when N'2¹59' = N'2159' collate Latin1_General_BIN then 1 else 0 end

This will select 0 as you'd expect.

More importantly, since MS SQL understands unicode properly, you can do this:

select cast(N'2¹59' as varchar)

which will give you '2159' - properly replacing the "broken" digits.

If you have no other option, you could also build a helper table to handle indexing the string (just a single column with numbers 1..1000 for example), and do something like this:

exists 
 (
  select 1 from [Numbers] 
  where 
   [Numbers].[Index] < len([Value]) + 1
    and 
   unicode(substring([Value], [Numbers].[Index], 1)) > 127
 )

Needless to say, this is going to be rather slow. For simple integers, though, this can work as a decent validation - simply use (unicode(substring([Value], [Numbers].[Index], 1)) not between 48 and 57) and ([Numbers].[Index] <> 0 or substring([Value], 1, 1) <> '-')) for example.

Upvotes: 1

Sagar Shirke
Sagar Shirke

Reputation: 686

From SQL SERVER 2012 there is a new function which have been added called TRY_PARSE, If you use it then it will automatically make non int to null.

select TRY_PARSE('2¹59' as int)

Output of above query will be null.

Upvotes: 1

Related Questions