asked
Data in the field that do not meet some criteria
Hi all, I have a table which has a field called PINNO. it is a varchar with a lenght of 11. I some data in the table. The data in this field should match the below; 1. Should have a lenght of 11 Characters 2. The first and last Character should be Alphabet i.e be a letter A to Z 3. The rest should be numeric What is required is data like **A123456789F** I need to extract data that do not meet this criteria but already saved. Can someone help me. Thanks
Here's an example of how to find those patterns. I use a case statement to evaluate each condition, and return a non-zero value if they match. Simply checking for this value 0 is enough to filter. You could put these columns into select statement too to give you an indication of which rule failed. declare @YourTable table (PINNO varchar(11)) insert into @YourTable select 'A123456789F' insert into @YourTable select 'A12347890F' insert into @YourTable select 'A1234567897' insert into @YourTable select '9123456789F' insert into @YourTable select 'A1234B6789F' select PINNO from @YourTable where case when len(PINNO) 11 then 1 when patindex('[^A-Z]%', upper(PINNO)) 0 then 2 when patindex('%[^A-Z]', upper(PINNO)) 0 then 3 when patindex('%[^0-9]%',substring(PINNO, 2,9)) 0 then 4 end 0
14 People are following this question.
