validation on a column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

validation on a column

Author

Topic

rajpes
Starting Member

13 Posts

Posted - 2010-02-08 : 06:58:32

hi, i want to display all the rows in a table if the specified column of the table contains any character anywhere which is not in the range [a-z] [0-9] [~-*],
that means any chinese or any language character must be displayed(returned )

conside A-Z(capitals) also

Kristen
Test

22859 Posts

Posted - 2010-02-08 : 07:00:58

[code]SELECT Col1, Col2
FROM MyTable
WHERE TestColumn LIKE '%[^-~*a-z0-9]%' COLLATE xyz
[/code]
You will need to use a collation that is suitable for the characters the column contains (e.g. Chinese) but is BINary / Case Sensitive (otherwise [a-z] will match [A-Z] also)

rajpes
Starting Member

13 Posts

Posted - 2010-02-08 : 07:35:21

DECLARE @ntext nvarchar(max)
set @ntext= N'Café'

SELECT @ntext
WHERE @ntext LIKE '%[^-~*a-z0-9]%' COLLATE Latin1_General_CS_AS

THIS IS NOT GIVING ANY RESULT!

rajpes
Starting Member

13 Posts

Posted - 2010-02-08 : 07:36:24

I DONT KNOW WHICH COLLATE TO USE!

Kristen
Test

22859 Posts

Posted - 2010-02-08 : 07:59:16

I don't know anything about using various collations and UNICODE character sets etc., but here's a test rig for anyone to try:


DECLARE @temp TABLE
(
	MyID	int IDENTITY(1,1) NOT NULL,
	MyNText nvarchar(10) COLLATE Latin1_General_CS_AS
)

INSERT INTO @temp(MyNText)
SELECT	N'Café' UNION ALL	-- Caps + Accent
SELECT	N'café' UNION ALL	-- Accent
SELECT	N'Cafe' UNION ALL	-- Caps
SELECT	N'cafe'			-- All lower case, no accents

SELECT	MyNText,
	[Bin] = CASE WHEN MyNText LIKE '%[^-~*a-z0-9]%' COLLATE Latin1_General_BIN
		THEN 'X'
		ELSE ''
		END,
	[CS_AS] = CASE WHEN MyNText LIKE '%[^-~*a-z0-9]%' COLLATE Latin1_General_CS_AS
		THEN 'X'
		ELSE ''
		END,
	[SQL CS_AS] = CASE WHEN MyNText LIKE '%[^-~*a-z0-9]%' COLLATE SQL_Latin1_General_CP1_CS_AS
		THEN 'X'
		ELSE ''
		END
FROM	@temp
ORDER BY MyID

Note that BINARY collation, as I suggested earlier, does work but I suspect that will have other issues (e.g. with non-Roman character sets

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-08 : 08:33:10

quote:
Originally posted by rajpes

I DONT KNOW WHICH COLLATE TO USE!

As a a courteous gesture could you please refrain from using block letters while posting?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources