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

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
Asked:
ainaks25
  • 6
  • 4
1 Solution
 
auke_tCommented:
--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
 
auke_tCommented:
--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
 
ainaks25Author Commented:
I created a function.

print dbo.udf_part_numeric('fieldName,';',3) gives an error " Invalid object name 'dbo.udf_part_numeric"
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
ainaks25Author Commented:
is there any way to convert the string to an array and use something like FieldName.split(";")[5]
0
 
auke_tCommented:
--Oops, should have been:

print dbo.udf_part_decimal('fieldName,';',3)
0
 
auke_tCommented:
print dbo.udf_part_decimal(fieldname, ';', 3)
0
 
ainaks25Author 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
 
auke_tCommented:
--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
 
auke_tCommented:
--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
 
ainaks25Author 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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

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