Reputation: 105
I have a table of this manner:
+---------+--------------------+
|ID | Component |
+---------+--------------------+
|00241147 | 000000001000245598 |
|00241147 | 000000001000090069 |
|00249207 | 000000002510256707 |
|00249208 | 000000002510245146 |
+---------+--------------------+
I want to select only those rows where Component is starting with '1'.
I'm using the following code:
select * from Table where Component like '%1%'
Upvotes: 3
Views: 77
Reputation: 50173
Cast them as bigint and read 1 using left()
function
select * from Table where left(cast(Component as bigint), 1) = 1
Note : This above assumes that Component column has varchar datatype
EDIT : Thanks for making demo for clarification by Uwe Keim
http://sqlfiddle.com/#!6/1987d/4
Upvotes: 4
Reputation: 4056
Try this:
SELECT * FROM Table
WHERE SUBSTRING(str_col, PATINDEX('%[^0]%', str_col+'.'), LEN(str_col)) LIKE '1%';
Upvotes: 2