Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 544
  • Last Modified:

parse a string separated by hyphens

I have a field that has an unknown number of numbers separated by hyphens.  I need to select each of the numbers and insert them into their own field.  How can I do this with sql?
Thank you!
0
may11998
Asked:
may11998
1 Solution
 
Victor SpiridonovCommented:
You can't do it with one sql statement, you should use stored procedure with cursor to perform this operation. Soemthing like (for 3 values ):

CREATE PROCEDURE TEST
as
DECLARE @combo_field varchar(255)
DECLARE @v1 VARCHAR(255)
DECLARE @v2 VARCHAR(255)
DECLARE @v3 VARCHAR(255)

DECLARE c CURSOR FOR
SELECT  combo_field
FROM t
OPEN c
FETCH NEXT FROM c  into @combo_field
WHILE @@fetch_status<>-1
BEGIN
IF @@fetch_statua<>-2
BEGIN
SELECT @v1=SUBSTRING(@combo_field,1,CHARINDEX(";",@combo_field)-1)
SELECT @combo_field=SUBSTIRNG(@combo_field,CHARINDEX(";",@combo_field)+1,DATALENGTH(@combo_field)-DATALENGTH(@v1)-1)
SELECT @v2=SUBSTRING(@combo_field,1,CHARINDEX(";",@combo_field)-1)
SELECT @combo_field=SUBSTIRNG(@combo_field,CHARINDEX(";",@combo_field)+1,DATALENGTH(@combo_field)-DATALENGTH(@v2)-1)
SELECT @v3=@combo_field
UPDATE t
SET v1=@v1,v2=@v2,v3=@v3
WHERE combo_field=@combo_field
end
FETCH NEXT FROM c  into @combo_field
end


0
 
may11998Author Commented:
Thanks!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now