April 21, 2010 at 2:40 am
Hi,
Is there any datatype to support only alphabets?
Can we achieve that through any constriaints?
Thanks In Advance
April 21, 2010 at 2:48 am
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://www.sqlservercentral.com/Forums/Topic904707-338-1.aspx#bm904716
U can write expression for only Alphabets....
Regards,
Chowdary
April 21, 2010 at 3:03 am
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
April 21, 2010 at 3:19 am
This may work better
CONSTRAINT CHK_TextCol CHECK (TextCol NOT LIKE '%[^Aa-Zz]%')
April 21, 2010 at 4:06 am
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]%')
April 21, 2010 at 4:16 am
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]%')
April 21, 2010 at 4:34 am
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. 🙂
April 21, 2010 at 4:50 am
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply