Split string on only first occurance of character/delimiter

Question

I've been searching all morning for this.

My knowledge of SQL Server is not excellent, and I'm out of answers.

Concrete examples are: City-Of-Style or Part1-Part2.

I need to split these examples into City and Of-Style and Part1 and Part2.

I figured out this little piece of code, but it switches part1 and part2 if the string contains a '-'.

PARSENAME(REPLACE('sample-string', '-', '.'), 1))

Any help on accomplishing this (preferably without a 200 lines function) is greatly appreciated.

Solution 1

If I understand correctly this will do the job; Click here for the fiddle

DECLARE @s VARCHAR(50)= 'City-Of-Style'

SELECT SUBSTRING(@s,0,CHARINDEX('-',@s,0)) AS firstPart,
    SUBSTRING(@s,CHARINDEX('-',@s,0)+1,LEN(@s)) AS secondPart

Solution 2

In SQL, you can use the split_part function.

E.g. cast(split_part(column,'-',1) as text) as new_column

Input: king-adfad-adfadd
Output: king

Solution 3

If there are no dashes in the string, you get a blank. The below snippet gives you the first part if there's a dash, otherwise the whole string.

DECLARE @TextIn VARCHAR(50)= 'City-Of-Style'
DECLARE @TextOut VARCHAR(500)
SELECT CASE WHEN CHARINDEX('-',@TextIn)>0 THEN SUBSTRING(@TextIn,0,CHARINDEX('-',@TextIn,0)) ELSE @TextIn END 

Comments

[Lynn]

SQL Server v14 split_part is not available. "Not a built-in function"