Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to check characters from string Expand / Collapse
Author
Message
Posted Thursday, December 12, 2013 5:46 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 3:52 AM
Points: 131, Visits: 248
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!
Post #1522259
Posted Thursday, December 12, 2013 5:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:27 PM
Points: 12,744, Visits: 31,080
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1522262
Posted Thursday, December 12, 2013 6:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 17, 2014 9:49 AM
Points: 6,798, Visits: 6,272
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.

Post #1522265
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse