question

Mcrono avatar image
Mcrono 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
tablemssqlcharacters
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

'A1234567890F' is 12 chars?
0 Likes 0 ·
Sorry it should be A123456789F
0 Likes 0 ·

1 Answer

·
Kev Riley avatar image
Kev Riley answered
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
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Many thanks Kev Riley. IT works.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.