• 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?
0
Webboy2008
Asked:
Webboy2008
  • 2
1 Solution
 
AmmarRCommented:
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
0
 
GhunaimaCommented:
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)) 
	else 
		set @y = ''

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

	return (ltrim(rtrim(@y)))
end

Open in new window

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

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