Solved

split varchar field into decimals

Posted on 2006-06-29
10
809 Views
Last Modified: 2011-04-14
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
Comment
Question by:ainaks25
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 
LVL 9

Expert Comment

by:auke_t
ID: 17009448
--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
 
LVL 9

Expert Comment

by:auke_t
ID: 17009458
--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 Comment

by:ainaks25
ID: 17009818
I created a function.

print dbo.udf_part_numeric('fieldName,';',3) gives an error " Invalid object name 'dbo.udf_part_numeric"
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:ainaks25
ID: 17009892
is there any way to convert the string to an array and use something like FieldName.split(";")[5]
0
 
LVL 9

Expert Comment

by:auke_t
ID: 17010297
--Oops, should have been:

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

Expert Comment

by:auke_t
ID: 17010307
print dbo.udf_part_decimal(fieldname, ';', 3)
0
 

Author Comment

by:ainaks25
ID: 17010583
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
 
LVL 9

Expert Comment

by:auke_t
ID: 17010718
--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
 
LVL 9

Accepted Solution

by:
auke_t earned 125 total points
ID: 17010726
--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 Comment

by:ainaks25
ID: 17010806
perfect,

thank you
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question