|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Finding "invalid" characters in a string7 other digits to make up the entire code. There was no checking on insert to make sure the code followed these rules. So in order to clean up the data, we need to find out which codes need to be fixed. I thought the following query would work, but the server is case-insensitive so it unfortunately does not: SELECT * FROM TableName WHERE UPPER(SUBSTRING(ID,2,7)) <> LOWER(SUBSTRING(ID,2,7)) Now this of course does not take special characters into account either, but it was a start. Any ideas on the best way to do it? I tried it with a bunch of REPLACE statements to change all digits to a particular character and look for that constant, but that seems like overkill. Thanks Jim Use a case-sensitive collation in the query: look up COLLATE in Books Online.
Maybe something like this (although I can't find a link between your narrative and the query): .... where (ID collate Latin1_General_CS_AS <> upper(ID) collate Latin1_General_CS_AS) .... ML --- http://milambda.blogspot.com/ How about something like this:
select * from MyTable where ID like 'P[0-9][0-9][0-9][0-9][0-9][0-9][0-9]' This should bring you all the records with ID that begins with the letter P (or p) fallowed by exactly 7 digits Adi Jim Coyne wrote: Show quote > We have a column whose data is suppose to start with a "P" and then contain > 7 other digits to make up the entire code. There was no checking on insert > to make sure the code followed these rules. So in order to clean up the > data, we need to find out which codes need to be fixed. > > I thought the following query would work, but the server is case-insensitive > so it unfortunately does not: > > SELECT * FROM TableName WHERE UPPER(SUBSTRING(ID,2,7)) <> > LOWER(SUBSTRING(ID,2,7)) > > Now this of course does not take special characters into account either, but > it was a start. > > Any ideas on the best way to do it? I tried it with a bunch of REPLACE > statements to change all digits to a particular character and look for that > constant, but that seems like overkill. > > Thanks > > Jim That's it. Thanks!!!
Show quote "Adi" <adico***@netvision.net.il> wrote in message news:1155281805.442916.232490@h48g2000cwc.googlegroups.com... > How about something like this: > > select * from MyTable where ID like > 'P[0-9][0-9][0-9][0-9][0-9][0-9][0-9]' > > This should bring you all the records with ID that begins with the > letter P (or p) fallowed by exactly 7 digits > > Adi > > Jim Coyne wrote: >> We have a column whose data is suppose to start with a "P" and then >> contain >> 7 other digits to make up the entire code. There was no checking on >> insert >> to make sure the code followed these rules. So in order to clean up the >> data, we need to find out which codes need to be fixed. >> >> I thought the following query would work, but the server is >> case-insensitive >> so it unfortunately does not: >> >> SELECT * FROM TableName WHERE UPPER(SUBSTRING(ID,2,7)) <> >> LOWER(SUBSTRING(ID,2,7)) >> >> Now this of course does not take special characters into account either, >> but >> it was a start. >> >> Any ideas on the best way to do it? I tried it with a bunch of REPLACE >> statements to change all digits to a particular character and look for >> that >> constant, but that seems like overkill. >> >> Thanks >> >> Jim >  |
|||||||||||||||||||||||
Other interesting topics