asked on

SQL Select - How to checks a column that contains numerics in first 3 positions

I need to SQL Select that can check if column has numerics in the first 3 positions.

My Table: AltCode
Test1
123Blah
4Peak
Hery*1

In the example about, I need a SQL Select Query that would identify rows:
123Blah
4Peak

Pawan Kumar

Hi Tmajor,
Just created a full tested solution for you-

--

CREATE TABLE Alts
(
	Vals VARCHAR(1000)
)
GO

INSERT INTO Alts VALUES
('AltCode'),              
('Test1'), 
('123Blah'),
('4Peak'),
('Hery*1'),
('1'),
('b')


--SOLUTION 1
SELECT * FROM Alts
WHERE LEFT(Vals,3) LIKE '%[0-9]%' 

--SOLUTION 2
SELECT * FROM Alts
WHERE SUBSTRING(Vals,1,3) LIKE '%[0-9]%' 

--

Open in new window

OUTPUT

/*------------------------

--SOLUTION 1
SELECT * FROM Alts
WHERE LEFT(Vals,3) LIKE '%[0-9]%' 

--SOLUTION 2
SELECT * FROM Alts
WHERE SUBSTRING(Vals,1,3) LIKE '%[0-9]%' 
------------------------*/
Vals
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
123Blah
4Peak
1

(3 row(s) affected)

Vals
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
123Blah
4Peak
1

(3 row(s) affected)

Open in new window

Hope it helps!

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

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

Start Free Trial

Pawan Kumar

I need to SQL Select that can check if column has numerics in the first 3 positions.

This clearly means you have to check numeric values in the first 3 positions.

Hi Tmajor,
Please refer my tested solution which perfectly cater to your above problem.

Regards,
Pawan

Vitor Montalvão

And this clearly shows that the author also wants anything that starts with a number:
"In the example about, I need a SQL Select Query that would identify rows:
123Blah
4Peak"

So, by the logic what he needs is anything starting with a number no matter is if only the first or the first three.

Pawan Kumar

The author has clearly written - I need to SQL Select that can check if column has numerics in the first 3 positions..

Scott Pletcher

To see if any of the first three chars contain a numeric digit (0 thru 9):

SELECT ...
FROM MyTable
WHERE PATINDEX('%[0-9]%', AltCode) BETWEEN 1 AND 3

Pawan Kumar

@Tmajor,

Your question says something and the answer you selected says something else. The answer you selected will not work if the value is A12YU

Only my solution and Scott's answer will work in any case.