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

  • 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?
  • 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

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