• Status: Solved
• Priority: Medium
• Security: Public
• Views: 838

split varchar field into decimals

i have a field that is varchar(252) and looks like "12;12.01;12.04;0;0;12.1 " I need to split that string with separated by " ; " values into decimals and use them for other calculations.
For example how to do : 3 * (value #6) which is (3 * 12.1) ?

thanks
0
ainaks25
• 6
• 4
1 Solution

Commented:
--First create this function:

CREATE function [dbo].udf_part
(
@imput varchar(100),
@delimiter varchar(1),
@part integer
)
returns varchar(2000) as
begin
declare @temp varchar(2000)
set @temp = @imput

while @part > 1
begin
set @temp = substring(@temp, patindex('%' + @delimiter + '%', @temp + @delimiter) + 1, 2000)
set @part = @part - 1
end
return (substring(@temp, 1, patindex('%' + @delimiter + '%', @temp + @delimiter) - 1))
end
GO

--after that you can use:

print convert(decimal(18,2),dbo.udf_part('12;12.01;12.04;0;0;12.1',';',3))

--and you could put the convert in the user defined function as well!
0

Commented:
--so it would become:

CREATE function [dbo].udf_part_decimal
(
@imput varchar(100),
@delimiter varchar(1),
@part integer
)
returns varchar(2000) as
begin
declare @temp varchar(2000)
set @temp = @imput

while @part > 1
begin
set @temp = substring(@temp, patindex('%' + @delimiter + '%', @temp + @delimiter) + 1, 2000)
set @part = @part - 1
end
return convert(decimal(18,2),(substring(@temp, 1, patindex('%' + @delimiter + '%', @temp + @delimiter) - 1)))
end
GO

--and then you could do:

print dbo.udf_part_numeric('12;12.01;12.04;0;0;12.1',';',3)
0

Author Commented:
I created a function.

print dbo.udf_part_numeric('fieldName,';',3) gives an error " Invalid object name 'dbo.udf_part_numeric"
0

Author Commented:
is there any way to convert the string to an array and use something like FieldName.split(";")[5]
0

Commented:
--Oops, should have been:

print dbo.udf_part_decimal('fieldName,';',3)
0

Commented:
print dbo.udf_part_decimal(fieldname, ';', 3)
0

Author Commented:
it worked but it's still returning varchar, so when I try to use it in calculations "dbo.udf_part_decimal(fieldName, ';', 6)/100 "  i get an error  "Syntax error converting the varchar value '69.00' to a column of data type int."
0

Commented:
--sorry, my fault!, don't have a sqlserver here at the moment ;-)
--First create this function:

CREATE function [dbo].udf_part
(
@imput varchar(100),
@delimiter varchar(1),
@part integer
)
returns decimal(18,2) as
begin
declare @temp varchar(2000)
set @temp = @imput

while @part > 1
begin
set @temp = substring(@temp, patindex('%' + @delimiter + '%', @temp + @delimiter) + 1, 2000)
set @part = @part - 1
end
return (substring(@temp, 1, patindex('%' + @delimiter + '%', @temp + @delimiter) - 1))
end
GO

--after that you can use:

print convert(decimal(18,2),dbo.udf_part('12;12.01;12.04;0;0;12.1',';',3))

--and you could put the convert in the user defined function as well!

Comment from auke_t
Date: 06/29/2006 06:50AM PDT
Comment

--so it would become:

CREATE function [dbo].udf_part_decimal
(
@imput varchar(100),
@delimiter varchar(1),
@part integer
)
returns varchar(2000) as
begin
declare @temp varchar(2000)
set @temp = @imput

while @part > 1
begin
set @temp = substring(@temp, patindex('%' + @delimiter + '%', @temp + @delimiter) + 1, 2000)
set @part = @part - 1
end
return convert(decimal(18,2),(substring(@temp, 1, patindex('%' + @delimiter + '%', @temp + @delimiter) - 1)))
end
GO
0

Commented:
--argh... still wrong:

CREATE function [dbo].udf_part_decimal
(
@imput varchar(100),
@delimiter varchar(1),
@part integer
)
returns decimal(18,2) as
begin
declare @temp varchar(2000)
set @temp = @imput

while @part > 1
begin
set @temp = substring(@temp, patindex('%' + @delimiter + '%', @temp + @delimiter) + 1, 2000)
set @part = @part - 1
end
return convert(decimal(18,2),(substring(@temp, 1, patindex('%' + @delimiter + '%', @temp + @delimiter) - 1)))
end
GO

--and then you could do:

print dbo.udf_part_numeric('12;12.01;12.04;0;0;12.1',';',3)
0

Author Commented:
perfect,

thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Featured Post

• 6
• 4
Tackle projects and never again get stuck behind a technical roadblock.