CODEWITHSUNDEEP
sqlsql-serversql-server-2005
André Alçada Padez
André Alçada Padez

Reputation: 11598

SQL - Select Uppercase fields

i have a table, that has a column (SQL_Latin1_General_CP1_CI_AI), and has a couple of hundred rows where that column is filled in all uppercase. I would like to know if it is possible to select all the rows that have that field in uppercase and, ultimately if i can make an update to capitalize the fields.

I'm using SQL Management Studio on MSSQL 2005

Thanks

Upvotes: 1

Views: 895

Answers (1)

Martin Smith
Martin Smith

Reputation: 453328

DECLARE @T TABLE
(
col VARCHAR(3) COLLATE SQL_Latin1_General_CP1_CI_AI
)

INSERT INTO @T 
SELECT 'foo' UNION ALL SELECT 'BAR' UNION ALL SELECT ''

UPDATE @T
SET col = STUFF(LOWER(col),1,1,LEFT(col,1)) 
WHERE col = UPPER(col) COLLATE SQL_Latin1_General_CP1_CS_AS AND LEN(col) >1

SELECT * FROM @T

Returns 

(1 row(s) affected)

col
----
foo
Bar


(3 row(s) affected)

Upvotes: 1

Related Questions