0

I understand that people have asked this question in other ways before but i'm not interested in pushing a single string though a variable.

I have a column containing data like: QWERTYUIOP-1,QWERTYUIOP-XL, ASDFG-2, HGJFK-SM. The column is called product. I need to be able to extract text after the '-' so i can store it as size in another column. for this instance lets call the table products.

How can you do this in SQL Server? It's pretty simple in postgreSQL with split_part but there doesnt seem to be a similar function other than string_split, and that only works for tables. When i try to use it against a column it says string_split doesn't exists.

Can someone help me out with this please?

JK1993
  • 148
  • 11

3 Answers3

1

One method is to use STUFF to remove leading characters, and use CHARINDEX to find where those leading characters end:

SELECT V.YourString,
       STUFF(V.YourString,1,CHARINDEX('-',V.YourString),'') AS TrailingCharacters
FROM (VALUES('QWERTYUIOP-1'),('QWERTYUIOP-XL'),('ASDFG-2'),('HGJFK-SM'))V(YourString)

OK, now that the goal posts have moved. You can amend the above to get the position of the last - character using REVERSE instead:

SELECT V.YourString,
       STUFF(V.YourString,1,LEN(V.YourString) - CHARINDEX('-',REVERSE(V.YourString))+1,'') AS TrailingCharacters
FROM (VALUES('QWERTYUIOP-1'),('QWERTYUIOP-XL'),('ASDFG-2'),('HGJFK-SM'),('QWER-WETT-EWT-1'))V(YourString)

Alternatively, you could use a string splitter like DelimitedSplit8k_LEAD and get the "last" row:

WITH CTE AS(
    SELECT V.YourString,
           DS.ItemNumber,
           DS.Item,
           ROW_NUMBER() OVER (PARTITION BY V.YourString ORDER BY DS.ItemNumber DESC) AS RN
    FROM (VALUES('QWERTYUIOP-1'),('QWERTYUIOP-XL'),('ASDFG-2'),('HGJFK-SM'),('QWER-WETT-EWT-1'))V(YourString)
         CROSS APPLY dbo.DelimitedSplit8K(V.YourString,'-') DS)
SELECT YourString,
       DS.Item
FROM CTE
WHERE RN = 1;
Thom A
  • 88,727
  • 11
  • 45
  • 75
  • Hi thanks for this, what if the product is like this QWER-WETT-EWT-1 and i only want to retrieve the end text still? – JK1993 Jun 23 '20 at 09:21
  • 1
    This is why providing **representative** sample data is important, @JK1993 . An example like that should have been in your question so that users don't "waste" time giving answers that aren't going to answer the *Real* question. – Thom A Jun 23 '20 at 09:22
  • Sorry if you feel you've wasted your time and thanks for the help that worked! – JK1993 Jun 23 '20 at 09:32
1

You can use combination of String_split and JSON_VALUE to split the value into multiple columns.

DECLARE @productSize table(ProductName VARCHAR(500))
INSERT INTO @productSize values
('QWERTYUIOP-1,QWERTYUIOP-XL, ASDFG-2, HGJFK-SM')

SELECT 
JSON_VALUE(concat('["',replace(value,'-','","'),'"]'),'$[0]') as productname,
JSON_VALUE(concat('["',replace(value,'-','","'),'"]'),'$[1]') as productSize
from @productSize
cross apply STRING_SPLIT(ProductName,',') 

+-------------+-------------+
| productname | productSize |
+-------------+-------------+
| QWERTYUIOP  | 1           |
| QWERTYUIOP  | XL          |
|  ASDFG      | 2           |
|  HGJFK      | SM          |
+-------------+-------------+
Venkataraman R
  • 12,181
  • 2
  • 31
  • 58
0
select 
 left(product,charindex('-',product)-1) product_code
 ,right(product,len(product)-charindex('-',product)) product_size
from (values('QWERTYUIOP-1'),('QWERTYUIOP-XL'),('ASDFG-2'),('HGJFK-SM')) p (product)

Do not use STRING_SPLIT for your purpose, because the string is split into rows and

The output rows might be in any order. The order is not guaranteed to match the order of the substrings in the input string.

https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15

So you'll never know which is left(code) and which is right(size).

shoek
  • 380
  • 2
  • 9