Init cap

Question

Init cap

bruce_reid

SSCrazy

Points: 2419
More actions
December 15, 2003 at 12:36 pm

#81703

I have a column fname in my table aaa.There are some names with initial letter as capital
letter.(David,Andrew,John...) AND some of them starts with small alphabets(tom,steve,bruce)
,how can I write a queryIf I want to get the names start with small letters .
Thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

It's been a while since I done that conversion, therefore I'm not 100% sure, but I think something like this would work



DROP TABLE myName

GO

CREATE TABLE myName(

myn char(10))

go

INSERT INTO myName (myn) VALUES('alfred')

INSERT INTO myName (myn) VALUES('Alfred')

INSERT INTO myName (myn) VALUES('berta')

INSERT INTO myName (myn) VALUES('Frank')

INSERT INTO myName (myn) VALUES('zap')

INSERT INTO myName (myn) VALUES('Zap')





SELECT 

*, ASCII(LEFT(myn,1)) 

FROM 

myName 

WHERE 

ASCII(LEFT(myn,1))>96 

ORDER BY 

ASCII(LEFT(myn,1)) 

DESC



DROP TABLE myName

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Rodrigo Acosta SSC-Insane Points: 21261 More actions · Answer 2

Another way could be this one, but don't know if it is cost efective:

DROP TABLE myNameGOCREATE TABLE myName(myn char(10))goINSERT INTO myName (myn) VALUES('alfred')INSERT INTO myName (myn) VALUES('Alfred')INSERT INTO myName (myn) VALUES('berta')INSERT INTO myName (myn) VALUES('Frank')INSERT INTO myName (myn) VALUES('zap')INSERT INTO myName (myn) VALUES('Zap')

SELECT UPPER(LEFT(myn, 1)) + SUBSTRING(myn, 2, LEN(myn)) FROM MyName

Rodrigo Acosta SSC-Insane Points: 21261 More actions · Answer 3

Sorry for the mess, I meant this:

DROP TABLE myName

GO

CREATE TABLE myName(myn char(10))

go

INSERT INTO myName (myn) VALUES('alfred')

INSERT INTO myName (myn) VALUES('Alfred')

INSERT INTO myName (myn) VALUES('berta')

INSERT INTO myName (myn) VALUES('Frank')

INSERT INTO myName (myn) VALUES('zap')

INSERT INTO myName (myn) VALUES('Zap')

GO

SELECT UPPER(LEFT(myn, 1)) + SUBSTRING(myn, 2, LEN(myn)) FROM MyName

GO

DROP TABLE MyName

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Ähem, I'm a little bit confused.

I thought the original question was how to select all those first name that do not start with an upper letter, or?

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Jonathan SSC-Insane Points: 20427 More actions · Answer 5

quote:

It's been a while since I done that conversion, therefore I'm not 100% sure, but I think something like this would work



DROP TABLE myName

GO

CREATE TABLE myName(

myn char(10))

go

INSERT INTO myName (myn) VALUES('alfred')

INSERT INTO myName (myn) VALUES('Alfred')

INSERT INTO myName (myn) VALUES('berta')

INSERT INTO myName (myn) VALUES('Frank')

INSERT INTO myName (myn) VALUES('zap')

INSERT INTO myName (myn) VALUES('Zap')





SELECT 

*, ASCII(LEFT(myn,1)) 

FROM 

myName 

WHERE 

ASCII(LEFT(myn,1))>96 

ORDER BY 

ASCII(LEFT(myn,1)) 

DESC



DROP TABLE myName

Frank

http://www.insidesql.de

http://www.familienzirkus.de

As the ASCII() just works on the leftmost character of a string, you don't need to use the LEFT() function:

SELECT FName

FROM YourTable

WHERE ASCII(FName) > 96

It's this simple only if the column is char or varchar and the collation supports this, of course...

--Jonathan

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

Good to know for the future and hopefully I don't need to do that conversion stuff no more.

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Rodrigo Acosta SSC-Insane Points: 21261 More actions · Answer 7

Right Frank.

It was my mistake. Pay no atention to my answer.

gljjr SSChampion Points: 10080 More actions · Answer 8

Give this a try Bruce,



SELECT fName

FROM Names

WHERE fName COLLATE Latin1_General_BIN LIKE ('[a-z]%')

Basically this is using psuedo regular expressions inside the Like function. I've added the collate clause so that the field compares differently for upper and lower case.

Gary Johnson

Microsoft Natural Language Group

DBA, Sr. DB Engineer

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.