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"
[Solved] Query extract size from last 2 numbers after dash
-
- Posts: 54
- Joined: Sun Sep 04, 2016 9:40 pm
[Solved] Query extract size from last 2 numbers after dash
Last edited by cartoonjazz on Thu Dec 19, 2019 5:51 pm, edited 1 time in total.
openoffice 4.1.2 windows 10
Re: Query extract size from last 2 numbers after dash in sku
You wrote a Query as:
Please try / use the following SQL ( Structured Query Language ):
Explanation: According to HSQL 1.8 Documenation:
Sliderule
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
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"
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"
I hope this helps, please be sure to let me / us know.HSQL 1.8 Documentation wrote:
RIGHT
RIGHT(string, int)
Returns the rightmost number of characters.
Example:
RIGHT(NAME, 3)
Sliderule
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
-
- Posts: 54
- Joined: Sun Sep 04, 2016 9:40 pm
Re: Query extract size from last 2 numbers after dash in sku
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?
openoffice 4.1.2 windows 10
Re: Query extract size from last 2 numbers after dash in sku
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"
Sliderule
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
-
- Posts: 54
- Joined: Sun Sep 04, 2016 9:40 pm
Re: [Solved] Query extract size from last 2 numbers after da
That worked, thank you very much
openoffice 4.1.2 windows 10