SQL Query for rows with substring value greater than

Hi…

I have a table in an MS SQL DB with nearly 15 million rows and need to update a small subset but the ID that I need to extract is a substring.

The keys values are all ‘RX1-######’ and I need to extract a set where the number section after the ‘RX1-’ is > 977289 but I don’t want to have to fetch all 14 million rows to update about 240.

Is there a query that I can put into the DQ Query reader node that will be able to get just these rows out of the database?

tC/.

I’m sure you can do it in a where clause in sql with functions but I suspect it will somewhat slow as certainly no index will be used.
Functions: substring with start at character 5 (if 0-based index hence use 4) and then cast to int and compare to your magic number

WHERE cast(SUBSTRING(key_field, 5, 6) AS int) >977289

(instead of 6 you have to replace with actual length of the numeric part).

Note this is untested pseudo code. Maybe it works, maybe you need to adjust it a bit.

2 Likes

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.