Login or Sign Up to become a member!

EXPERTS, INFORMATION, IDEAS & KNOWLEDGE

Social bookmarker Add this

Your profile

Search

November 2008
Mon Tue Wed Thu Fri Sat Sun
 << <   > >>
          1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30

XML Feeds

« Sybase IQ Is A Columnar Database, Why Should I Care?How to rename a column in a SQL Server table without using the designer »
The Data Management Journal

Three Ways To Return All Rows That Contain Uppercase Characters Only

by SQLDenis


Permalink 24 Jun 2008 12:26 , Categories: Data Modelling & Design

How do you select all the rows that contain uppercase characters only? There sre three ways to do this
1 Compare with BINARY_CHECKSUM
2 Use COLLATE
3 Cast to varbinary

Let’s first create the table and also some test data

CREATE TABLE #tmp ( x VARCHAR(10) NOT NULL )

  1. INSERT INTO #tmp
  2. SELECT ‘Word’ UNION ALL
  3. SELECT ‘WORD’ UNION ALL
  4. SELECT ‘ABC’ UNION ALL
  5. SELECT ‘AbC’ UNION ALL
  6. SELECT ‘ZxZ’ UNION ALL
  7. SELECT ‘ZZZ’ UNION ALL
  8. SELECT ‘word’

if we want only the uppercase columns then this is supposed to be our output

WORD
ABC
ZZZ

Let’s get started, first up is BINARY_CHECKSUM

  1. SELECT x
  2. FROM #TMP
  3. WHERE BINARY_CHECKSUM(x) = BINARY_CHECKSUM(UPPER(x))

Second is COLLATE

  1. SELECT x
  2. FROM #TMP
  3. WHERE x = UPPER(x) COLLATE SQL_Latin1_General_CP1_CS_AS

Third is Cast to varbinary

  1. SELECT x
  2. FROM #TMP
  3. WHERE CAST(x AS VARBINARY(10)) = CAST(UPPER(x) AS VARBINARY(10))

Of course if you database is already case sensitive you can just do the following

  1. SELECT x
  2. FROM #TMP
  3. WHERE UPPER(x) = x

That will work, how do you find out what collation was used when your database was created? You can use DATABASEPROPERTYEX for that. I use the model DB here because when you create a new DB by default it inherits all the properties from the model DB.
When I run this

  1. SELECT DATABASEPROPERTYEX( ‘model’ , ‘collation’ )

I get this as output: SQL_Latin1_General_CP1_CI_AS

What does all that junk mean? Well let’s run the following function (yes those are 2 colons :: )

  1. SELECT *
  2. FROM ::fn_helpcollations ()
  3. WHERE NAME =’SQL_Latin1_General_CP1_CI_AS’

The description column contains this info

Latin1-General, case-insensitive, accent-sensitive,
kanatype-insensitive, width-insensitive for Unicode Data,
SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data

You can read some more info about Selecting a SQL Collation here: http://msdn2.microsoft.com/en-us/library/aa176552(SQL.80).aspx

Leave a comment »Send a trackback » 186 views

Trackback address for this post

Trackback URL (right click and copy shortcut/link location)

No feedback yet

Leave a comment


Your email address will not be revealed on this site.

Your URL will be displayed.
PoorExcellent
(Line breaks become <br />)
(Name, email & website)
(Allow users to contact you through a message form (your email will not be revealed.)