Solved

# split varchar field into decimals

Posted on 2006-06-29
767 Views
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
Question by:ainaks25
• 6
• 4

LVL 9

Expert Comment

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

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

ID: 17009818
I created a function.

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

Author Comment

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

ID: 17010297
--Oops, should have been:

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

LVL 9

Expert Comment

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

Author Comment

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

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

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

ID: 17010806
perfect,

thank you
0

## Featured Post

### Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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.