How to find if column which should contain english data has other data – SQL server

SQL Server natively does not support regular expressions. One needs to use .net library and it is not great experience as you might have a database where sqlclr might not be enabled. Morever – nobody will give quick access to .net ide etc on that machine or you might not be familiar with .net , so can’t code up quick routine for the same. So when you are stuck like this as I was, where do you turn? You dig deep into the manual (implement RTFM).

SQL has following function

ASCII ( character_expression ) 

Returns –  int

Returns the ASCII code value of the leftmost character of a character expression.

This means one can run query like

select
ac_no ac ,
part_no part ,
count(1) total
from ElectorName
where
lang = 1 and
(
(ascii(fm_name)  not between 65 and 122) or
(ascii(rln_fm_nm) not between 65 and 122)
)
group by ac_no,part_no
order by ac_no, part_no asc
Here fm_name, rln_fm_name are nvarchar columns. This saved me a day as I had inadvertantly dropped the constraints to accomodate data problems.
Lesson to self – Never agree to compromise on data integrity, however unpleasant you might become due to that stand. It is  like a lie – once you say one, you make up ten more to keep the first one. In the process whole application collapses like house of cards.

		
How to find if column which should contain english data has other data – SQL server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s