Solved

SQL Expression

Posted on 2013-01-09
4
170 Views
Last Modified: 2013-01-09
Hello experts,

I have a need to query some data from our Item master table.  However, the item_id consists of mixed numer, alpha, and alphanumeric.

I only wants to pull the alphanumeric numbers.  The first 3 or more length of the item_id needs to be alpha follow some numbers.

Example:

122929099Klkls  <--don't pull
TTED992929        <--This is the one I want
2939392090        <--don't pull

With the above how can I construct my query to pull only alphanumeric item_id and the first 3 characters are more needs to be alpha?

SELECT ITEM_ID, DESCRIPTION FROM INVENTORY
WHERE SUBSTRING(ITEM_ID, 1, 3) LIKE ('[A-Za-z]%')

Can't get the above to look at least the first 3 characters to make sure it's Alpha.  It's pulling A12322323 into my report.  I need to have at least 3 letters or more followed by numbers.
0
Comment
Question by:holemania
4 Comments
 
LVL 18

Assisted Solution

by:Cluskitt
Cluskitt earned 166 total points
ID: 38759039
SELECT ITEM_ID, DESCRIPTION FROM INVENTORY
WHERE SUBSTRING(ITEM_ID, 1, 3) LIKE ('[A-Za-z][A-Za-z][A-Za-z]%[0-9][0-9][0-9]')

first 3 being letters and last 3 being numbers
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 167 total points
ID: 38759048
Try this:

SELECT ITEM_ID, DESCRIPTION FROM INVENTORY
WHERE ITEM_ID LIKE ('[A-Za-z][A-Za-z][A-Za-z]%')
0
 
LVL 5

Accepted Solution

by:
RehanYousaf earned 167 total points
ID: 38759252
I think
WHERE ITEM_ID LIKE ('[A-Za-z][A-Za-z][A-Za-z]%[0-9]')
will suffice
0
 

Author Closing Comment

by:holemania
ID: 38759690
Thanks.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
consolidate 4 lines of oracle query output to 1 line 4 44
Select2 jquery help 9 46
ms sql stored procedure 22 77
sql query Help 12 34
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now