How to compare a part of a string using like function SQL

Question

I'm not sure if like function can be used to compare strings or if there is another function to achieve this but this is my case, I have the below part:

R71-14-40000-ATN-LH-D-PF, for the third segment (40000) which is the length; the first 3 digits are the integer part and last 2 digits are the decimals.

I would like to get all parts from DB where the length (third segment) is equals or greater than that value for example, if I use the above part I should get the yellow values an omit the other ones (the values can also be R71-14-50000-ATN-LH-D-PF, R71-14-55000-ATN-LH-D-PF, R71-14-60000-ATN-LH-D-PF, not only start with 4 etc).

I tried this PartNum like '%R71-14-%-ATN-LH-D-PF%' but I get all parts no matter its third segment value

What DBMS (SQL Server, MySQL, Oracle, etc.) are you using? Please tag your question appropriately — Nick
– Nick, Commented May 25, 2020 at 23:38

Gordon Linoff · Accepted Answer · 2020-05-25 23:39:48Z

2

You can use a substring, I think:

where substring(col, 8, 5) >= substring('R71-14-40000-ATN-LH-D-PF', 8, 5)

Some databases use substr() rather than substring().

answered May 25, 2020 at 23:39

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

jarlh Over a year ago

And the ANSI/ISO version is substring(col from 8 for 5).

user2864740 · Accepted Answer · 2020-05-26 03:40:35Z

Using a more restriction LIKE value such as

PartNum LIKE 'R71-14-4____-ATN-LH-D-PF'

would answer the particular query for "values with the 3rd-segment starting with a 4". It could also be ..14-4%-ATN.., although I chose the _ match-exactly-one wildcard for explicitness of a fixed 3rd-segment length (5); it's also easier for the engine to match against.

Then expanding to for "equals or greater than 4" under this fixed-width data can be done by choosing the 3rd-segment starting with a 4, or 5, or 6..

PartNum LIKE 'R71-14-[456789]____-ATN-LH-D-PF'

This works in SQL Server, although there might be slight variations in different RDMBS implementations. This approach is lexical based, which works fine on single-character integer values even though it does not use/utilize numeric equality. SQL Server also supports character-negations that can be useful - see the documentation for the specific RDBMS.

The leading and trailing % are not needed per the shown data. Using a leading % can also be very detrimental to index usage.

The trailing % makes more sense if not caring about the remaining segments,

PartNum LIKE 'R71-14-[456789]____-%'

And if needing to only care about the 3rd-segment,

PartNum LIKE '___-__-[456789]____-%'
PartNum LIKE '___-__-[456789]%'      -- or even this

Note the difference from the original query (..14-%-ATN..), which matches all values as expected. This is because it does not add any restrictions to the 3rd-segment value.

Collectives™ on Stack Overflow

How to compare a part of a string using like function SQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related