Query not working in SQL Server

Question

I have table 'A' with column 'col' with values:

a.b.1.c

a.b.2.c

a.b.26.d

If I execute this Select it works:

SELECT
*
FROM
A
WHERE col LIKE 'a.b.[2-9]%';

If I execute this one, it does'nt:

SELECT
*
FROM
A
WHERE col LIKE 'a.b.[2-9][0-9]*%';

The expected result of the query should be:

a.b.2.c

a.b.26.d

So, I need the "*" to be optional the second number

Why is this happening? How could I make it work to allow numbers greater than 9?

Here are both fiddle1 fiddle2:

if the [0-9] is optional and you require the results to include a.b.2.c, then what's wrong with your first statement ? Just curious to know :) — DataWrangler
– DataWrangler, Commented Jul 12, 2017 at 13:50

score 3 · Accepted Answer · 2017-07-12 13:42:12Z

3

not needed. Try this;

select * from a
WHERE col LIKE 'a.b.[2-9][0-9]%' or col like 'a.b.[2-9]%'

edited Jul 12, 2017 at 13:42

answered Jul 12, 2017 at 13:38

user1529235

Sign up to request clarification or add additional context in comments.

1 Comment

Mayday Over a year ago

But I need it to be optional, it should also return me the a.b.2.c

Vecchiasignora · Accepted Answer · 2017-07-12 13:41:59Z

2

Try without * and use or operator

SELECT
*
FROM
A
WHERE col LIKE 'a.b.[2-9]%' or col LIKE 'a.b.[2-9][0-9]%';

answered Jul 12, 2017 at 13:41

Vecchiasignora

1,3157 silver badges6 bronze badges

Comments

Krystian Kozłowski · Accepted Answer · 2017-07-12 13:38:13Z

1

Try this (without *):

SELECT
*
FROM
A
WHERE col LIKE 'a.b.[2-9][0-9]%';

answered Jul 12, 2017 at 13:38

Krystian Kozłowski

1623 bronze badges

1 Comment

Mayday Over a year ago

But I need it to be optional, it should also return me the a.b.2.c

Nenad Zivkovic · Accepted Answer · 2017-07-12 13:52:33Z

0

SELECT * FROM A WHERE col LIKE 'a.b.[2-9][0*-9]%';

answered Jul 12, 2017 at 13:52

Nenad Zivkovic

18.6k6 gold badges45 silver badges58 bronze badges

Collectives™ on Stack Overflow

Query not working in SQL Server

4 Answers 4

1 Comment

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related