datatype for alphabets

Question

Post reply

datatype for alphabets

Rechana Rajan

SSCertifiable

Points: 7678
More actions
April 21, 2010 at 2:40 am

#218141

Hi,

Is there any datatype to support only alphabets?

Can we achieve that through any constriaints?

Thanks In Advance

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

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

Pulivarthi Sasidhar SSCertifiable Points: 6706 More actions · Answer 1

hi,

There is no such datatype available......

To do like that plz go thru the forum "Select records with Alphanumeric Values" which will give good knowledge...

or click the link given below

http://qa.sqlservercentral.com/Forums/Topic904707-338-1.aspx#bm904716

U can write expression for only Alphabets....

Regards,

Chowdary

Dohsan SSCrazy Points: 2972 More actions · Answer 2

This is only a basic example but you could use a CHECK constraint on the column to prevent non alpha types from being inserting into the table.

(Example Below does not check for characters like %$^ so would have to be expanded)



IF OBJECT_ID(N'CharTest', N'U') IS NOT NULL

 DROP TABLE CharTest

GO

CREATE TABLE CharTest

	(

		TextCol CHAR(3)

		CONSTRAINT CHK_TextCol CHECK (TextCol NOT LIKE '%[0-9]%')

	)

INSERT INTO CharTest (TextCol)

VALUES ('ABC')

INSERT INTO CharTest (TextCol)

VALUES ('CBA')

INSERT INTO CharTest (TextCol)

VALUES ('1BC')

INSERT INTO CharTest (TextCol)

VALUES ('AB1')

INSERT INTO CharTest (TextCol)

VALUES ('1B$')

INSERT INTO CharTest (TextCol)

VALUES ('$BC')

SELECT TextCol FROM CharTest

Dohsan SSCrazy Points: 2972 More actions · Answer 3

Dohsan

SSCrazy

Points: 2972

April 21, 2010 at 3:19 am

#1155210

This may work better



CONSTRAINT CHK_TextCol CHECK (TextCol NOT LIKE '%[^Aa-Zz]%')

Paul White SSC Guru Points: 150457 More actions · Answer 4

Paul White

SSC Guru

Points: 150457

April 21, 2010 at 4:06 am

#1155222

Dohsan (4/21/2010)

This may work better

CONSTRAINT CHK_TextCol CHECK (TextCol NOT LIKE '%[^Aa-Zz]%')

To work with a binary collation, you'd need something like:

CONSTRAINT CHK_TextCol CHECK (col1 LIKE '%[a-z]%' OR col1 LIKE '%[A-Z]%')

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Dohsan SSCrazy Points: 2972 More actions · Answer 5

Dohsan

SSCrazy

Points: 2972

April 21, 2010 at 4:16 am

#1155230

I was just reading up about possible collation issues using [Aa-Zz] etc, would it make much difference to performance to list them all?



CONSTRAINT CHK_TextCol CHECK (TextCol NOT LIKE '%[^abcedefghijklmnopqrstuvwxyzABCEDEFGHIJKLMNOPQRSTUVWXYZ]%')

Paul White SSC Guru Points: 150457 More actions · Answer 6

Dohsan (4/21/2010)

I was just reading up about possible collation issues using [Aa-Zz] etc, would it make much difference to performance to list them all?

CONSTRAINT CHK_TextCol CHECK (TextCol NOT LIKE '%[^abcedefghijklmnopqrstuvwxyzABCEDEFGHIJKLMNOPQRSTUVWXYZ]%')

That works just fine. 🙂

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Dohsan SSCrazy Points: 2972 More actions · Answer 7

Thanks to Paul it should now be a little more robust when dealing with certain collations



IF OBJECT_ID(N'CharTest', N'U') IS NOT NULL

 DROP TABLE CharTest

GO

CREATE TABLE CharTest

	(

		/* Using "full text" on the check constraint rather than '%[^Aa-Zz]%'

		should avoid Binary collation issues [Uncomment Below to Check] */

		TextCol CHAR(3) --COLLATE Latin1_General_BIN

		CONSTRAINT CHK_TextCol CHECK (TextCol NOT LIKE '%[^abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ]%')

--		CONSTRAINT CHK_TextCol CHECK (TextCol NOT LIKE '%[^Aa-Zz]%')

	)

INSERT INTO CharTest (TextCol)

VALUES ('abc')

INSERT INTO CharTest (TextCol)

VALUES ('ABC')

INSERT INTO CharTest (TextCol)

VALUES ('CBA')

INSERT INTO CharTest (TextCol)

VALUES ('1BC')

INSERT INTO CharTest (TextCol)

VALUES ('AB1')

INSERT INTO CharTest (TextCol)

VALUES ('1B$')

INSERT INTO CharTest (TextCol)

VALUES ('$BC')

SELECT TextCol FROM CharTest