SQL Regular expression greater than

Question

I have a table, lets say table A, with data following same pattern:

a.b.1.c
a.b.2.c
a.b.3.c
a.b.3.d

How could I Select data when the number is greater than 1, for example?

Right know, I am using:

SELECT * FROM A WHERE col LIKE "%a.b.%"

And then, in java, I manage to get which ones are greater than a.b.1

Isn't possible to do this in a single query?

(I need to do it in MySql and SQLServer)

SQL Server does not have native support for regular expressions so you question doesn't make sense. Or, more accurately, you cannot do it in SQL Server using functionality it doesn't have. — Gordon Linoff, Jul 11 '17 at 12:44
SQL Server: You can do it in this way: `col LIKE '%a.b.[2-9]%'`. — Rokuto, Jul 11 '17 at 12:47
Read [Is storing a delimited list in a database column really that bad?](http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad), where you will see a lot of reasons why the answer to this question is **Absolutly yes!** — Zohar Peled, Jul 11 '17 at 12:47
In MS SQL you can split string into parts where 3. part may be converted into numeric value. Then you'll be able to compare them. — Maciej Los, Jul 11 '17 at 12:48
Ahh I see. Like does USE a regular expression, so I guess it pretty much counts — Milney, Jul 11 '17 at 12:49
Like does use pattern matching, but it's hardly regular expression. It's much more simple and not nearly as strong. — Zohar Peled, Jul 11 '17 at 13:12
The leading `%` in the `LIKE` expression will not be sargable so an index on the column can't be used efficiently. It's unclear if that's really needed in your query. Remove it if not needed to improve performance ; — Dan Guzman, Jul 11 '17 at 13:22

score 0 · Answer 1 · answered Jul 11 '17 at 13:10

If I understand your question correctly, you want to skip all rows where a.b.1.* and select data starting from a.b.2.*, so a.b.2 will be the first possible match for you, and query should look like this:

WITH dataset
AS
(
    SELECT 'a.b.1.c' AS List
    UNION ALL
    SELECT 'a.b.2.c'
    UNION ALL
    SELECT 'a.b.3.c'
    UNION ALL
    SELECT 'a.b.3.d'
)
SELECT * FROM dataset
WHERE List >= 'a.b.2'

SQL Regular expression greater than

1 Answers1