how to validate my string in sql

Question

how to validate my string in sql

raghuldrag

Hall of Fame

Points: 3160
More actions
October 25, 2017 at 3:45 am

#340908

Dear Friends,
I m validate one alphanumeric character string for learning purpose My character set is
vat='33AAACV'
Declare @vat varchar(10)
set vat='33AAACV'
select 'VALID' where left(@gstno,2) like '%[0-9]' and substring(@gstno,3,5) like '%[A-Z]'
The first two digits number only and the next 5 digits should contains only Alphabets
my query is working fine but if i changed from 5th(AA5CV) digit is replace into number its showing "Valid" kindly help me out how to restrict its should only alphabets?

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

raghuldrag - Wednesday, October 25, 2017 3:45 AM
my query is working fine

No it isn't, because it won't even parse. Please post DDL in the form of CREATE TABLE statements, sample data in the form of INSERT statements, your actual query and the results you expect based on your sample data.

John

SQLPirate SSCrazy Points: 2880 More actions · Answer 2

John Mitchell-245523 - Wednesday, October 25, 2017 4:08 AM
raghuldrag - Wednesday, October 25, 2017 3:45 AM
my query is working fine
No it isn't, because it won't even parse.

:laugh:

Raghuldrag, even when you get the syntax to parse, your use of the LIKE operator with wildcards is not correct for what you're trying to accomplish. Only the % character represents a string. Other wildcard characters - like [ ] - represent single characters.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

SQLPirate - Wednesday, October 25, 2017 7:49 AM
John Mitchell-245523 - Wednesday, October 25, 2017 4:08 AM
raghuldrag - Wednesday, October 25, 2017 3:45 AM
my query is working fine
No it isn't, because it won't even parse.
:laugh:
Raghuldrag, even when you get the syntax to parse, your use of the LIKE operator with wildcards is not correct for what you're trying to accomplish. Only the % character represents a string. Other wildcard characters - like [ ] - represent single characters.

Actually, I think he's got that bit right. LIKE '%[A-Z]' means any string ending in a character from A to Z.

Edit: oh, I see what you mean - the "[A-Z]" portion represents one single character. Yes.

John

Paulo de Jesus Default port Points: 1403 More actions · Answer 4

Maybe this is the LIKE syntax you are looking for as in this example:

DECLARE @String AS VARCHAR(7)

SET @String = '33AA5CV'

SELECT CASE WHEN @String LIKE '[0-9][0-9][A-Z][A-Z][A-Z][A-Z][A-Z]' THEN 'Yes' ELSE 'No' END

kramaswamy SSCoach Points: 18181 More actions · Answer 5

Paulo de Jesus - Wednesday, October 25, 2017 8:07 AM
Maybe this is the LIKE syntax you are looking for as in this example:
DECLARE @String AS VARCHAR(7)
SET @String = '33AA5CV'
SELECT CASE WHEN @String LIKE '[0-9][0-9][A-Z][A-Z][A-Z][A-Z][A-Z]' THEN 'Yes' ELSE 'No' END

I'd extend that one step further, given that the original query had VARCHAR(10) instead of VARCHAR(7), I'd go with:

DECLARE @String AS VARCHAR(10)

SET @String = '33AA5CV'

SELECT CASE WHEN LEFT(@String, 7) LIKE '[0-9][0-9][A-Z][A-Z][A-Z][A-Z][A-Z]' THEN 'Yes' ELSE 'No' END

Although, if this is something you plan on doing more frequently, you might want to consider using a RegEx instead. See This site for an example of how to implement RegEx in SQL Server.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

kramaswamy - Thursday, October 26, 2017 8:43 AM
I'd extend that one step further, given that the original query had VARCHAR(10) instead of VARCHAR(7), I'd go with:
DECLARE @String AS VARCHAR(10)
SET @String = '33AA5CV'
SELECT CASE WHEN LEFT(@String, 7) LIKE '[0-9][0-9][A-Z][A-Z][A-Z][A-Z][A-Z]' THEN 'Yes' ELSE 'No' END
Although, if this is something you plan on doing more frequently, you might want to consider using a RegEx instead. See This site for an example of how to implement RegEx in SQL Server.

Funny that you add so many characters when one was enough.
DECLARE @String AS VARCHAR(10)

SET @String = '33AA5CV'

SELECT CASE WHEN @String LIKE '[0-9][0-9][A-Z][A-Z][A-Z][A-Z][A-Z]%' THEN 'Yes' ELSE 'No' END

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 7

Luis Cazares - Thursday, October 26, 2017 9:03 AM
kramaswamy - Thursday, October 26, 2017 8:43 AM
I'd extend that one step further, given that the original query had VARCHAR(10) instead of VARCHAR(7), I'd go with:
DECLARE @String AS VARCHAR(10)
SET @String = '33AA5CV'
SELECT CASE WHEN LEFT(@String, 7) LIKE '[0-9][0-9][A-Z][A-Z][A-Z][A-Z][A-Z]' THEN 'Yes' ELSE 'No' END
Although, if this is something you plan on doing more frequently, you might want to consider using a RegEx instead. See This site for an example of how to implement RegEx in SQL Server.
Funny that you add so many characters when one was enough.
DECLARE @String AS VARCHAR(10)
SET @String = '33AA5CV'
SELECT CASE WHEN @String LIKE '[0-9][0-9][A-Z][A-Z][A-Z][A-Z][A-Z]%' THEN 'Yes' ELSE 'No' END

Your method is also SARGable, Luis.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)