I am using SQL Server 2008.

I have strings like this:

AB-123

CDW-32

declare @First_Part varchar(3)
declare @Second_Part varchar(5)

I want to split the string with delimiter '-' and store as first part and second part.

I saw few sample functions to split a string but these return table of values.

I simply want first part and second part.

In above examples context:

@First_Part = 'AB'

@Second_Part = '123'

Any simple way to do this?

Hi OldEnthusiast,

Use following table values function for this which is taking two parameter input param and delimiter.

Create FUNCTION dbo.[UFN_Split](          @sInputList VARCHAR(8000) -- 
List of delimited items        , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items      ) 
RETURNS @List TABLE 
(item VARCHAR(8000))           
 BEGIN      DECLARE @sItem VARCHAR(8000)    
  WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0      
 BEGIN       
SELECT      
  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))), 
   @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),
LEN(@sInputList))))              IF LEN(@sItem) > 0        INSERT INTO @List SELECT @sItem       
END            IF LEN(@sInputList) > 0       INSERT INTO @List SELECT @sInputList -- Put the last item in      
RETURN     
 END 

Declare @var varchar(50)='niraj-123'
Declare @tbl table (id int identity (1,1),value varchar(50))
insert into @tbl (value)select * from dbo.UFN_SPLIT (@var,'-') 
Declare @First_Part  varchar(50)  ,@Second_Part   varchar(50)
select @First_Part=Value from @tbl where id=1
select @Second_Part   =Value from @tbl where id=2
print @First_Part
print @Second_Part

You can use as per you requirement ,Hope this will help you !!!

Thanks Niraj.

I also had that approach in mind but was looking a way to avoid that tricky split function.

Anyway thanks a lot.

DECLARE @str nvarchar(21) = 'AB-123'

SELECT substring(@str, 1, charindex('-', @str) - 1) AS FirstPart,
       substring(@str, charindex('-', @str) + 1, len(@str)) AS SecondPart

Hi OldEnthusiast,

Some other trick for your reference.

DECLARE @OriginTbl TABLE(fullPart VARCHAR(99));
INSERT INTO @OriginTbl VALUES('AB-123'),('CDW-32');

DECLARE @TargetTbl TABLE(firstPart VARCHAR(99),secondPart VARCHAR(99));

INSERT INTO @TargetTbl
SELECT PARSENAME(REPLACE(fullPart,'-','.'),2),PARSENAME(REPLACE(fullPart,'-','.'),1)
FROM @OriginTbl

SELECT * FROM @TargetTbl

See PARSENAME

If you have any question, feel free to let me know.