[Solved] Query extract size from last 2 numbers after dash

[cartoonjazz]

Hi, noob here any help is appreciated my sql isn't good enough to understand the syntax. Trying to modify an existing sql script so that it extracts "SIZE" from "SKU". The "SIZE" is always the last 2 numbers after the final dash in the "SKU".

Currently this script only works if the size is after the first dash i.e. "abc-32" if the sku is "abc-abc-xys-32" then it doesn't work

SELECT SUBSTR( "SKU", 1, LOCATE( '-', "SKU" ) - 1 ) AS "STYLE", SUBSTR( "SKU", CASE WHEN LOCATE( 'Size', "SKU" ) = 0 THEN LOCATE( '-', "SKU" ) + 1 ELSE LOCATE( 'Size', "SKU" ) + 4 + 1 END ) AS "SIZE", "TABLE1"."SKU", "TABLE1"."PRICE", "TABLE1"."QUANTITY" FROM "TABLE1"

[Sliderule]

You wrote a Query as:

Code: Select all

SELECT 
   SUBSTR( "SKU", 1, LOCATE( '-', "SKU" ) - 1 ) AS "STYLE", 
   SUBSTR( "SKU", CASE WHEN LOCATE( 'Size', "SKU" ) = 0 THEN LOCATE( '-', "SKU" ) + 1 ELSE LOCATE( 'Size', "SKU" ) + 4 + 1 END ) AS "SIZE",
   "TABLE1"."SKU", 
   "TABLE1"."PRICE", 
   "TABLE1"."QUANTITY" 
FROM "TABLE1"

Please try / use the following SQL ( Structured Query Language ):

Code: Select all

SELECT 
   SUBSTR( "SKU", 1, LOCATE( '-', "SKU" ) - 1 ) AS "STYLE", 
   RIGHT( "SKU", 2) AS "SIZE",
   "TABLE1"."SKU", 
   "TABLE1"."PRICE", 
   "TABLE1"."QUANTITY" 
FROM "TABLE1"

Explanation: According to HSQL 1.8 Documenation:

RIGHT

RIGHT(string, int)

Returns the rightmost number of characters.

Example:
RIGHT(NAME, 3)

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[cartoonjazz]

There is another problem there are 2 possible skus one where the size in the sku are the last 2 digits OR when the sku has "-1" after the size at rightmost, i.e. "abc-xyz-32-1" where 32 is the size. I expect a simple if -1 = size then it has to use the 2 digits before the last 2, being "32", any suggestions?

[Sliderule]

Code: Select all

SELECT
   SUBSTR( "SKU", 1, LOCATE( '-', "SKU" ) - 1 ) AS "STYLE",
   CASE WHEN RIGHT("SKU",2) = '-1' THEN RIGHT(SUBSTR("SKU",1, LENGTH("SKU")-2), 2) ELSE RIGHT( "SKU", 2) END AS "SIZE",
   "TABLE1"."SKU",
   "TABLE1"."PRICE",
   "TABLE1"."QUANTITY"
FROM "TABLE1"

Additional suggestion, read and use the database back-end documentation.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[cartoonjazz]

That worked, thank you very much