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: 835
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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