Breaking string occurrence dynamically in SQL based on data

Question

I have the below data in a table. Now I need to separate the string based on the "-" value. The separation needs to be done based on the below output scenarios.

Name	Required Output
itim-connect-prd	CONNECT
WHATS-PRD	WHATS
Commitment-FinOps	COMMITMENT
itim-xbl-hml	XBL
itim-audit-hml	AUDIT
BSC-AWS-Accounts-Roots	AWS

For example, in the above table, the [Name] column is the source data and I need to convert the [Name] column to the [Required Output] column.

To get the above required output column, I have used the below SQL Script... But it didn't work for the scenarios like Commitment-FinOps&WHATS-PRD values.

SQL script used

UPPER(SUBSTRING(SUBSTRING([Name],CHARINDEX('-',[Name])+1,CHARINDEX('-', [Name], CHARINDEX('-', [Name]) + 1)),1,CHARINDEX('-',SUBSTRING([Name],CHARINDEX('-',[Name])+1,CHARINDEX('-', [Name], CHARINDEX('-', [Name]) + 1)))-1)) AS Required Output

Can anyone modify the script so it will work for all scenarios.

Answer 1

Apparently you want the first substring if there are two parts and second one if there are more. You can use a bunch of charindex:

select *
from (values
    ('itim-connect-prd'),
    ('WHATS-PRD'),
    ('Commitment-FinOps'),
    ('itim-xbl-hml'),
    ('itim-audit-hml'),
    ('BSC-AWS-Accounts-Roots')
) as t(name)
cross apply (
  select nullif(charindex('-', name), 0)
) as ca1(pos1)
cross apply (
  select nullif(charindex('-', name, pos1 + 1), 0)
) as ca2(pos2)
cross apply (
    select case
      when pos2 is null then substring(name, 1, pos1 - 1)
      else substring(name, pos1 + 1, pos2 - pos1 - 1)
    end
) as ca3(result)

In SQL Server 2022 you can use string_split.

Answer 2

Please try the following solution based on tokenization.

It is using SQL Server's XML and XQuery functionality.

Notable points:

• 1st CROSS APPLY is converting tokens column into XML.
• 2nd CROSS APPLY is counting number of tokens in the XML.
• XPath predicate /root/r[1] or /root/r[2] is retrieving needed token.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (id INT IDENTITY PRIMARY KEY, tokens VARCHAR(256));
INSERT @tbl (tokens) VALUES
('itim-connect-prd'),
('WHATS-PRD'),
('Commitment-FinOps'),
('itim-xbl-hml'),
('itim-audit-hml'),
('BSC-AWS-Accounts-Roots'),
('Commitment-FinOps&WHATS-PRD');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = '-';

SELECT *
    , result = UPPER(IIF(cnt=2
        , c.value('(/root/r[1]/text())[1]', 'VARCHAR(128)')
        , c.value('(/root/r[2]/text())[1]', 'VARCHAR(128)')))
FROM @tbl AS t
CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' + 
    REPLACE(tokens, @separator, ']]></r><r><![CDATA[') + 
    ']]></r></root>' AS XML)) AS t1(c)
CROSS APPLY (SELECT c.value('count(/root/r)', 'INT')) AS t2(cnt);

Output

id	tokens	result
1	itim-connect-prd	CONNECT
2	WHATS-PRD	WHATS
3	Commitment-FinOps	COMMITMENT
4	itim-xbl-hml	XBL
5	itim-audit-hml	AUDIT
6	BSC-AWS-Accounts-Roots	AWS
7	Commitment-FinOps&WHATS-PRD	FINOPS&WHATS