aspx, sql statement

Posted on 2011-05-09
Last Modified: 2012-06-27
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?
Question by:Webboy2008
    LVL 15

    Expert Comment

    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
    LVL 8

    Expert Comment

    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

    LVL 8

    Expert Comment

    Change "ALTER FUNCTION" with "CREATE FUNCTION" in the code
    LVL 21

    Accepted Solution


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Training Course: Java/J2EE and SOA

    This course will cover both core and advanced Java concepts like Database connectivity, Threads, Exception Handling, Collections, JSP, Servlets, XMLHandling, and more. You'll also learn various Java frameworks like Hibernate and Spring.

    Suggested Solutions

    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now