[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 294
  • 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

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

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