• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 302
  • Last Modified:

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)

0
sifuhall
Asked:
sifuhall
2 Solutions
 
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
 
NievergeltCommented:
This should work:

SELECT * FROM Table WHERE (field1 LIKE '[^0-9]') OR (field1 LIKE '%[^0-9][^0-9][^0-9]')
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now