inadvisableguy
inadvisableguy

Reputation: 105

How to use wildcard characters in this?

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

Answers (2)

Yogesh Sharma
Yogesh Sharma

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

Gaurang Dave
Gaurang Dave

Reputation: 4056

Try this:

SELECT * FROM Table 
WHERE SUBSTRING(str_col, PATINDEX('%[^0]%', str_col+'.'), LEN(str_col)) LIKE '1%';

Upvotes: 2

Related Questions