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

aspx, sql statement

I have a column called data. like John Chan,Peter Chan, Wayne Chan
It is in sql server database. Most of them has five items.

I just want to split them in sql statement.
Once it is splitted. I also want to make some insert statement
such as if item 1 is 'John Chan' update column1
If item 2 is Peter Chan' update column 2
and etc.

How can I execute in one sql statement?
  • 2
1 Solution
hi Webboy2008

am just trying to understand the question right

so you have a column called DATA with 5 rows
you want to read the rows and update the other 5 columns (column1, column2,column3 ...)

and check if they contain data just update otherwise insert.

is it correct, if not please explain
Create this function then you can get values by running a SQL query like SELECT distdata(data, 1) as part1, distdata(date, 2) as part2 .....

alter function distdata (@x as varchar(2000), @n int)
returns varchar(100) 
as begin
declare @y as varchar(2000), @nn int
set @y = @x
set @nn = 1
while @nn<@n BEGIN
	if charindex(',', @y)>0
		set @y = right(@y, len(@y)-charindex(',', @y)) 
		set @y = ''

	set @nn = @nn + 1
if charindex(',', @y)>0
	set @y = left(@y, charindex(',', @y)-1)

	return (ltrim(rtrim(@y)))

Open in new window

Change "ALTER FUNCTION" with "CREATE FUNCTION" in the code
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

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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