asked on

Pattern matching select

I am having trouble coming up with a method to achieve the following. I have tried patindex, which seems to be the way to go but I believe I do not fully understand how to achieve the results.

I need a select statement that will return all fields when field1 begins with a letter (not a number) and/or field1 ends with 3 letters (again, not numbers)

ASKER CERTIFIED SOLUTION

rafrancisco

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

obahat

SELECT *
FROM TableName
WHERE ISNUMERIC(LEFT(CAST(field1 AS VARCHAR), 1)) = 0
AND ISNUMERIC(RIGHT(CAST(field1 AS VARCHAR), 1)) = 0
AND ISNUMERIC(LEFT(RIGHT(CAST(field1 AS VARCHAR), 2),1)) = 0
AND ISNUMERIC(LEFT(RIGHT(CAST(field1 AS VARCHAR), 3),1)) = 0

The AND/OR depends on the conditions that you need (you specify AND/OR in the question, so... :) ).

Hope this help,

------------------------------------
SQL Farms Solutions
www.sqlfarms.com

SOLUTION

Nievergelt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sifuhall

ASKER

Thanks for the replies, however neither seem to catch when a field ends with 3 characters.

For example: with this data

a12345
aa12345
12345
12345a
12345aaa
a12345a
a12345aaa

I would want to return:
a12345 (begins with a letter)
aa12345 (begins with a letter)
12345aaa (ends with 3 letters)
a12345a (begins with a letter)
a12345aaa (begins with a letter)

and NOT return:
12345 (does not begin with a letter or end with 3 letters)
12345a (does not begin with a letter or end with 3 letters)

rafrancisco

This worked for me:

SELECT *
FROM YourTable
WHERE Field1 LIKE '[A-Z]%' OR Field1 LIKE '%[A-Z][A-Z][A-Z]'

sifuhall

ASKER

I have combined Nievergelt's answer with rafrancisco's answer to produce this, which seems to work:

SELECT field1
FROM TABLE
WHERE field1 LIKE '[A-Z]%'
OR field1 LIKE '%[^0-9][^0-9][^0-9]'

jrb1

SELECT field1
FROM TABLE
WHERE field1 LIKE '[A-Z]%'
OR field1 LIKE '%[A-Z][A-Z][A-Z]'

That doesn't work? You should be able to positively look for alpha rather than not numeric. If the value ever ends in another charater (.-+,;: etc) they will produce different results.