CODEWITHSUNDEEP
sql-serverstringcomparesql-like
Pedro Rodriguez
Pedro Rodriguez

Reputation: 51

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

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).

enter image description here

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

Upvotes: 2

Views: 58

Answers (2)

user2864740
user2864740

Reputation: 61985

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.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271003

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().

Upvotes: 2

Related Questions