Tags: case, character, column, contains, entries, example, number, programming, sql, table, vin

finding lower case data

On Programmer » SQL

4,753 words with 4 Comments; publish: Thu, 29 May 2008 01:02:00 GMT; (20078.13, « »)

I have a table with a VIN number column, I need to find all entries where the

VIN contains a lower case character, example '4d0131P2X4E146985'. This record

should be included in the results because it contains 'd'.. What would be the

easiest way to write a query to return these, can I use regular expression in

T-SQL...?

Thanks,

Dan

All Comments

Leave a comment...

  • 4 Comments
    • Alien2_51,

      Check the view INFORMATION_SCHEMA.COLUMNS to see the collation of the column

      in question. I guess it is using a case insensitive one. Use the COLLATE

      clause to tell SQL Server which collation to use during the operation and use

      a case sensitive one. You can also use function binary_checksum.

      select *

      from

      (select '4d0131P2X4E146985' as c1

      union all

      select '4D0131P2X4E146985' as c1

      ) as t1

      where c1 != upper(c1) collate SQL_Latin1_General_CP1_CS_AS

      select *

      from

      (select '4d0131P2X4E146985' as c1

      union all

      select '4D0131P2X4E146985' as c1

      ) as t1

      where binary_checksum(c1) != binary_checksum(upper(c1))

      AMB

      "Alien2_51" wrote:

      > I have a table with a VIN number column, I need to find all entries where the

      > VIN contains a lower case character, example '4d0131P2X4E146985'. This record

      > should be included in the results because it contains 'd'.. What would be the

      > easiest way to write a query to return these, can I use regular expression in

      > T-SQL...?

      > Thanks,

      > Dan

      #1; Thu, 29 May 2008 01:04:00 GMT
    • You could do this:

      select VIN

      from yourTable

      where VIN like '%[abcdefghijklmnopqrstuvwxyz]%'

      collate Latin1_General_CS_AS

      -- or

      select VIN

      from yourTable

      where VIN like '%[a-z]%'

      collate Latin1_General_BIN

      Note that it will not work to use the range [a-z] in the first

      example, because in the CS_AS collation, [a-z] contains the

      uppercase letters A through Y.

      Steve Kass

      Drew University

      http://www.stevekass.com

      Alien2_51 wrote:

      >I have a table with a VIN number column, I need to find all entries where the

      >VIN contains a lower case character, example '4d0131P2X4E146985'. This record

      >should be included in the results because it contains 'd'.. What would be the

      >easiest way to write a query to return these, can I use regular expression in

      >T-SQL...?

      >Thanks,

      >Dan

      >

      #2; Thu, 29 May 2008 01:05:00 GMT
    • Thanks Alejandro... Just what I needed, and so simple...

      DB

      "Alejandro Mesa" wrote:

      > Alien2_51,

      > Check the view INFORMATION_SCHEMA.COLUMNS to see the collation of the column

      > in question. I guess it is using a case insensitive one. Use the COLLATE

      > clause to tell SQL Server which collation to use during the operation and use

      > a case sensitive one. You can also use function binary_checksum.

      > select *

      > from

      > (select '4d0131P2X4E146985' as c1

      > union all

      > select '4D0131P2X4E146985' as c1

      > ) as t1

      > where c1 != upper(c1) collate SQL_Latin1_General_CP1_CS_AS

      > select *

      > from

      > (select '4d0131P2X4E146985' as c1

      > union all

      > select '4D0131P2X4E146985' as c1

      > ) as t1

      > where binary_checksum(c1) != binary_checksum(upper(c1))

      >

      > AMB

      >

      > "Alien2_51" wrote:

      > > I have a table with a VIN number column, I need to find all entries where the

      > > VIN contains a lower case character, example '4d0131P2X4E146985'. This record

      > > should be included in the results because it contains 'd'.. What would be the

      > > easiest way to write a query to return these, can I use regular expression in

      > > T-SQL...?

      > >

      > > Thanks,

      > >

      > > Dan

      #3; Thu, 29 May 2008 01:06:00 GMT
    • Alien, I guess you might want to check this out

      http://vadivel.blogspot.com/2003/12/doing-case-sensitive-searches.html

      Best Regards

      Vadivel

      http://vadivel.blogspot.com

      "Alien2_51" wrote:

      > I have a table with a VIN number column, I need to find all entries where the

      > VIN contains a lower case character, example '4d0131P2X4E146985'. This record

      > should be included in the results because it contains 'd'.. What would be the

      > easiest way to write a query to return these, can I use regular expression in

      > T-SQL...?

      > Thanks,

      > Dan

      #4; Thu, 29 May 2008 01:07:00 GMT