Solved

# Pattern matching select

Posted on 2005-04-15
297 Views
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)

0
Question by:sifuhall

LVL 28

Accepted Solution

Try

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

LVL 5

Expert Comment

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

LVL 7

Assisted Solution

This should work:

SELECT * FROM Table WHERE (field1 LIKE '[^0-9]') OR (field1 LIKE '%[^0-9][^0-9][^0-9]')
0

LVL 1

Author Comment

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

LVL 28

Expert Comment

This worked for me:

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

LVL 1

Author Comment

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

LVL 25

Expert Comment

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

## Featured Post

This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be \$37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.