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)

LVL 1
sifuhallAsked:
Who is Participating?
 
rafranciscoCommented:
Try

SELECT *
FROM YourTable
WHERE Field1 LIKE '[A-Z]%[A-Z][A-Z][A-Z]'
0
 
obahatCommented:

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
0
 
NievergeltSenior SW DevCommented:
This should work:

SELECT * FROM Table WHERE (field1 LIKE '[^0-9]') OR (field1 LIKE '%[^0-9][^0-9][^0-9]')
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
sifuhallAuthor Commented:
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)
0
 
rafranciscoCommented:
This worked for me:

SELECT *
FROM YourTable
WHERE Field1 LIKE '[A-Z]%' OR Field1 LIKE '%[A-Z][A-Z][A-Z]'
0
 
sifuhallAuthor Commented:
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]'
0
 
jrb1Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.