How to check characters from string

Question

How to check characters from string

avdhut.k

SSCrazy

Points: 2834
More actions
December 12, 2013 at 5:46 am

#280107

Hi,
i am using sql server 2008 R2,
My requirement is as follows,
I had a string ABCRD1234E
I want to write query to check following things,
1) The length of the string should be minimum 10 digits.
2) First 5 characters must be letters.
3) Next 4 character must be numbers.
4) Next 1 Character must be letter.
5)The fourth letter must be either P,F,R,C,A,H,B,J or L.
First one will achived using Len function,
can any one help me ,for other points.
Thanks in Advance!

Viewing 3 posts - 1 through 2 (of 2 total)

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

Lowell SSC Guru Points: 323506 More actions · Answer 1

there is some support for patterns/regular expressions in SQL

this does what you are asking, i think. one expression tests exactly ten characters, the other test the first ten characters

WITH mySampleData(val)

AS

(

SELECT 'ABCRD1234E' UNION ALL

SELECT 'ABCRD1234EOTHERDATA' UNION ALL

select RIGHT(NEWID(),10) UNION ALL

select RIGHT(NEWID(),10)

)

SELECT *,

CASE WHEN val LIKE'[A-Z][A-Z][A-Z][PFRCAHBJL][A-Z][0-9][0-9][0-9][0-9][A-Z]' THEN 1 ELSE 0 END AS Match,

CASE WHEN val LIKE'[A-Z][A-Z][A-Z][PFRCAHBJL][A-Z][0-9][0-9][0-9][0-9][A-Z]%' THEN 1 ELSE 0 END AS Match

FROM mySampleData

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

David Burrows SSC Guru Points: 65138 More actions · Answer 2

Lowell (12/12/2013)
there is some support for patterns/regular expressions in SQL
this does what you are asking, i think. one expression tests exactly ten characters, the other test the first ten characters
WITH mySampleData(val)
AS
(
SELECT 'ABCRD1234E' UNION ALL
SELECT 'ABCRD1234EOTHERDATA' UNION ALL
select RIGHT(NEWID(),10) UNION ALL
select RIGHT(NEWID(),10)
)
SELECT *,
CASE WHEN val LIKE'[A-Z][A-Z][A-Z][PFRCAHBJL][A-Z][0-9][0-9][0-9][0-9][A-Z]' THEN 1 ELSE 0 END AS Match,
CASE WHEN val LIKE'[A-Z][A-Z][A-Z][PFRCAHBJL][A-Z][0-9][0-9][0-9][0-9][A-Z]%' THEN 1 ELSE 0 END AS Match
FROM mySampleData

The second CASE statement will fit the requirements

And could be used to find non complient data by

SELECT *

FROM mySampleData

WHERE val NOT LIKE'[A-Z][A-Z][A-Z][PFRCAHBJL][A-Z][0-9][0-9][0-9][0-9][A-Z]%'

Far away is close at hand in the images of elsewhere.
Anon.