String Validation - 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

SQL Server 2012 Forums

SSIS and Import/Export (2012)

String Validation

Author

Topic

swoozie
Starting Member

25 Posts

Posted - 2014-03-12 : 09:58:05

I am unable to validate on the User side, so I have to validate contents of a field on the backend during my processing.

I have a Field that is 8 Characters, 2 are Alpha, and 6 are numeric

I need to validate the following, and though there was a simpler way than a check for each using a format\mask

--Does not start with 2 Alpha Character
--Contains a #
--Not the right length (8 characters)
--Invalid Format, contains other characters

I was thinking something like [A-Z][A-z]######

But I am not sure what the syntax would be.

Thanks,

Susan

swoozie
Starting Member

25 Posts

Posted - 2014-03-12 : 10:42:40

Answer - Sometimes you just need to put on glasses.

SELECT Item
FROM Table
WHERE ITEM NOT LIKE '[a-z][a-z][0-9][0-9][0-9][0-9][0-9][0-9]'

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-03-12 : 10:43:33

You should use a double-negative logic, like in the example shown below.

DECLARE @x VARCHAR(8) = 'AB123456'

IF
		@x NOT LIKE '[^a-zA-z]_______'  -- 1st char
		AND @x NOT LIKE '_[^a-zA-z]______' -- 2nd char
		AND @x NOT LIKE '%[#]%' -- #
	PRINT 'Is valid'
ELSE
	PRINT 'Is not valid';

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources