12

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
voluminat0
  • 866
  • 2
  • 11
  • 21

4 Answers4

40

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
Kaf
  • 33,101
  • 7
  • 58
  • 78
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
Echilon
  • 10,064
  • 33
  • 131
  • 217
1

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

L_J
  • 2,351
  • 10
  • 23
  • 28
Tim
  • 27
  • 1
0

Building on the previous answers, I offer this as a solution - which provides both the first and last elements. If their is no delimiter found, the last element is empty.

DECLARE 
@del varchar(1) = '-',
@TextIn nvarchar(20) = 'City-Of-Style'
SELECT CASE WHEN CHARINDEX(@del,@TextIn)>0 THEN SUBSTRING(@TextIn,0,CHARINDEX(@del,@TextIn,0)) ELSE @TextIn END AS firstPart,
 CASE WHEN CHARINDEX(@del,@TextIn)>0 THEN SUBSTRING(@TextIn,CHARINDEX(@del,@TextIn,0)+1,LEN(@TextIn)) ELSE '' END AS secondPart

Or, if you're working in a stored procedure, you could assign the results to variables:

DECLARE 
@del nvarchar(1) = '-',
@firstPart nvarchar(100),
@lastPart nvarchar(100),
@TextIn nvarchar(20) = 'City-Of-Style'
SELECT @firstPart = (CASE WHEN CHARINDEX(@del,@TextIn)>0 THEN SUBSTRING(@TextIn,0,CHARINDEX(@del,@TextIn,0)) ELSE @TextIn END),
@lastPart = (CASE WHEN CHARINDEX(@del,@TextIn)>0 THEN SUBSTRING(@TextIn,CHARINDEX(@del,@TextIn,0)+1,LEN(@TextIn)) ELSE '' END)

select @firstPart, @lastPart
JonV
  • 493
  • 3
  • 15