How to use wildcard characters in this?

Question

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%'

See [Removing leading zeroes from a field in a SQL statement](https://stackoverflow.com/q/92093/107625), then do `LIKE '1%'` on the trimmed value. — Uwe Keim, Jan 09 '18 at 06:44

Yogesh Sharma · Accepted Answer · 2018-01-09T07:01:15.370

4

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

edited Jan 09 '18 at 07:01

answered Jan 09 '18 at 06:46

Yogesh Sharma

49,870
5
26
52

1

[Here is a working SQL Fiddle](http://sqlfiddle.com/#!6/1987d/4) with your above code. – Uwe Keim Jan 09 '18 at 06:55

score 2 · Answer 2 · edited Jan 09 '18 at 06:57

2

Try this:

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

edited Jan 09 '18 at 06:57

Uwe Keim

39,551
56
175
291

answered Jan 09 '18 at 06:47

Gaurang Dave

3,956
2
15
34

1

[Here is a working SQL Fiddle](http://sqlfiddle.com/#!6/1987d/7/0) based upon your above code. – Uwe Keim Jan 09 '18 at 06:57
1

its not loading but hope it helped you. – Gaurang Dave Jan 09 '18 at 07:19

How to use wildcard characters in this?

2 Answers2