?
Solved

asp.net sql

Posted on 2011-05-09
12
Medium Priority
?
256 Views
Last Modified: 2012-08-13

Data Example:

Purchased from quoteID: 344889,5000,5000,KB-1 DUAL BUILDING CONTRACTOR,KB-1 DUAL BUILDING CONTRACTOR    
Purchased from quoteID: 333147,5000,none,B2,none


Above two lines are in the same column called Data
In SQL, I would like to do something like


If (second item) and (third item) are numbers then
      update tablename
      set BA1 = second item,
          BA2 = third item

ElseIf (second item is number) and (third item = 'none') then
      update tablename
      set BA1 = second item

Elseif (second_item  = 'none') and (third item is number) then
      update tablename
      set BA2 = third

 
In above situation, how can I code in sql statement? Another issue is the column value has five items at all time.


500 points for completed and working sql statement in code
0
Comment
Question by:Webboy2008
  • 7
  • 4
12 Comments
 
LVL 9

Expert Comment

by:anillucky31
ID: 35725841
You have not given full detail about table structures. In which table you have stored Purchased from quoteID:

How you will determine that which  Purchased from quoteID: you have to pick for processing.

and after analyzing Purchased from quoteID: you want to update some table tablename. is there more condition to update. plz clarify more with genuine table structures  
0
 
LVL 21

Expert Comment

by:Alfred A.
ID: 35725926
You can put the following in a stored procedure.  Also, setup the provided split function in your database as well.  I just created this on top of my head and cursors was just the one on top of my head.  There are other ways to do this but this should work.  Note:  I haven't tested this but I believe this should work.
--Put the following in Stored Procedure

DECLARE @DataStr varchar(2000)

DECLARE PurchaseCursor CURSOR READ_ONLY FOR
SELECT Data FROM Purchased  -- I am not sure what is your table name but you put your query here.

OPEN PurchaseCursor

FETCH NEXT FROM SalesCursor
INTO @DataStr

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @SplitID int
    DECLARE @SplitString varchar(100)
    DECLARE @Str1 varchar(100)
    DECLARE @Str2 varchar(100)
    DECLARE @Str3 varchar(100)
    DECLARE @Str4 varchar(100)
    DECLARE @Str5 varchar(100)
    DECLARE SplitDataCursor CURSOR READ_ONLY FOR
    SELECT SplitID,Value FROM dbo.udf_Split(',',@DataStr)
    OPEN SplitDataCursor
    FETCH NEXT FROM SplitDataCursor
    INTO @SplitID, @SplitString
    WHILE @@FETCH_STATUS = 0
       IF @SplitID = 1
       BEGIN
           SET @Str1 = @SplitString
       END 
       IF @SplitID = 2
       BEGIN
           SET @Str2 = @SplitString
       END 
       IF @SplitID = 3
       BEGIN
           SET @Str3 = @SplitString
       END 
       IF @SplitID = 4
       BEGIN
           SET @Str4 = @SplitString
       END 
       IF @SplitID = 5
       BEGIN
           SET @Str5 = @SplitString
       END 
       FETCH NEXT FROM SplitDataCursor
       INTO @SplitID, @SplitString
    END
    CLOSE SplitDataCursor;
    DEALLOCATE SplitDataCursor;

    IF ISNUMERIC(@Str2) = 1 AND ISNUMERIC(@Str3) = 1
    BEGIN
        --update tablename
        --set BA1 = second item,
        --BA2 = third item
    END
    IF ISNUMERIC(@Str2) = 1 AND @Str3 = 'none'
    BEGIN
        --update tablename
        --set BA1 = second item
    END
    IF @Str2 = 'none' AND AND ISNUMERIC(@Str3) = 1
    BEGIN
        --update tablename
        --set BA1 = second item
    END

    FETCH NEXT FROM PurchaseCursor
    INTO @DataStr
END

CLOSE PurchaseCursor;
DEALLOCATE PurchaseCursor;

GO



-- Run this to create the split function in your database

CREATE FUNCTION dbo.udf_Split (@Separator char(1), @String varchar(2000))
RETURNS table
AS
RETURN (
    WITH Pieces(SplitID, start, stop) AS (
      SELECT 1, 1, CHARINDEX(@Separator, @String)
      UNION ALL
      SELECT SplitID + 1, stop + 1, CHARINDEX(@Separator, @String, stop + 1)
      FROM Pieces
      WHERE stop > 0
    )
    SELECT SplitID,
      SUBSTRING(@String, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS Value
    FROM Pieces
  )
GO

Open in new window

0
 

Author Comment

by:Webboy2008
ID: 35726074
Thank. But I prefer to have save as .sql and don't want to save function in DB for one time shot.
This is just data script update. Stored Procedure is my last option. SQL Statement is better to me.

You can make up the tablename and columnname.

Again, I appreicate your helps but I am not sql programmer so working code will be great.

Thanks
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 21

Expert Comment

by:Alfred A.
ID: 35726183
OK.  I understand.  For SQL Statement approach, you still need to have the split function in your database.  I just made up your tablename (Purchase) and columnname (Data) where Data for example has a value of "344889,5000,5000,KB-1 DUAL BUILDING CONTRACTOR,KB-1 DUAL BUILDING CONTRACTOR".  I am also assuming that "tablename" has a QuoteID that is related to QuoteID of "Purchase".
UPDATE tablename SET BA1 = (SELECT (SELECT Value FROM dbo.udf_Split(',',Data) WHERE SplitID = 2) FROM Purchase WHERE Purchase.QuoteID = tablename.QuoteID), 
                     BA2 = (SELECT (SELECT Value FROM dbo.udf_Split(',',Data) WHERE SplitID = 3) FROM Purchase WHERE Purchase.QuoteID = tablename.QuoteID)

WHERE ISNUMERIC((SELECT (SELECT Value FROM dbo.udf_Split(',',Data) WHERE SplitID = 2) FROM Purchase WHERE Purchase.QuoteID = tablename.QuoteID)) = 1
   AND ISNUMERIC((SELECT (SELECT Value FROM dbo.udf_Split(',',Data) WHERE SplitID = 3) FROM Purchase WHERE Purchase.QuoteID = tablename.QuoteID)) = 1

-----------------------------------------

UPDATE tablename SET BA1 = (SELECT (SELECT Value FROM dbo.udf_Split(',',Data) WHERE SplitID = 2) FROM Purchase WHERE Purchase.QuoteID = tablename.QuoteID)

WHERE ISNUMERIC((SELECT (SELECT Value FROM dbo.udf_Split(',',Data) WHERE SplitID = 2) FROM Purchase WHERE Purchase.QuoteID = tablename.QuoteID)) = 1
   AND (SELECT (SELECT Value FROM dbo.udf_Split(',',Data) WHERE SplitID = 3) FROM Purchase WHERE Purchase.QuoteID = tablename.QuoteID) = 'none'

-----------------------------------------

UPDATE tablename SET BA2 = (SELECT (SELECT Value FROM dbo.udf_Split(',',Data) WHERE SplitID = 3) FROM Purchase WHERE Purchase.QuoteID = tablename.QuoteID)

WHERE (SELECT (SELECT Value FROM dbo.udf_Split(',',Data) WHERE SplitID = 2) FROM Purchase WHERE Purchase.QuoteID = tablename.QuoteID) = 'none'
   AND ISNUMERIC((SELECT (SELECT Value FROM dbo.udf_Split(',',Data) WHERE SplitID = 3) FROM Purchase WHERE Purchase.QuoteID = tablename.QuoteID)) = 1

Open in new window

0
 
LVL 21

Expert Comment

by:Alfred A.
ID: 35726196
By the way, it will difficult to provide "working code" per se, if you don't provide specific table and column structure as @anillucky31 already mentioned.

I just provided as near as possible working code.  Without any specific information, I will stop at this point.
0
 

Author Comment

by:Webboy2008
ID: 35730861

The column called Note (e.g. holding the record like Purchased from quoteID: 333147,5000,none,B2,none

Table Name is called, tblData

BA1 is column name
BA2 is column name

We are only dealing with one table.
0
 
LVL 21

Expert Comment

by:Alfred A.
ID: 35731290
OK.  Try this.  Note: you will still need the split function.  Also, I haven't tested this.  There is a possibility that I might missed something.  Test it out.
UPDATE tblData SET BA1 = (SELECT (SELECT Value FROM dbo.udf_Split(',',RIGHT(LTRIM(RTRIM(Note)),LEN(LTRIM(RTRIM(Note)))-24)) WHERE SplitID = 2) FROM tblData), 
                   BA2 = (SELECT (SELECT Value FROM dbo.udf_Split(',',RIGHT(LTRIM(RTRIM(Note)),LEN(LTRIM(RTRIM(Note)))-24)) WHERE SplitID = 3) FROM tblData)

WHERE ISNUMERIC((SELECT (SELECT Value FROM dbo.udf_Split(',',RIGHT(LTRIM(RTRIM(Note)),LEN(LTRIM(RTRIM(Note)))-24)) WHERE SplitID = 2) FROM tblData)) = 1
  AND ISNUMERIC((SELECT (SELECT Value FROM dbo.udf_Split(',',RIGHT(LTRIM(RTRIM(Note)),LEN(LTRIM(RTRIM(Note)))-24)) WHERE SplitID = 3) FROM tblData)) = 1

-----------------------------------------

UPDATE tblData SET BA1 = (SELECT (SELECT Value FROM dbo.udf_Split(',',RIGHT(LTRIM(RTRIM(Note)),LEN(LTRIM(RTRIM(Note)))-24)) WHERE SplitID = 2) FROM tblData)

WHERE ISNUMERIC((SELECT (SELECT Value FROM dbo.udf_Split(',',RIGHT(LTRIM(RTRIM(Note)),LEN(LTRIM(RTRIM(Note)))-24)) WHERE SplitID = 2) FROM tblData)) = 1
            AND (SELECT (SELECT Value FROM dbo.udf_Split(',',RIGHT(LTRIM(RTRIM(Note)),LEN(LTRIM(RTRIM(Note)))-24)) WHERE SplitID = 3) FROM tblData) = 'none'

-----------------------------------------

UPDATE tblData SET BA2 = (SELECT (SELECT Value FROM dbo.udf_Split(',',RIGHT(LTRIM(RTRIM(Note)),LEN(LTRIM(RTRIM(Note)))-24)) WHERE SplitID = 3) FROM tblData)

WHERE           (SELECT (SELECT Value FROM dbo.udf_Split(',',RIGHT(LTRIM(RTRIM(Note)),LEN(LTRIM(RTRIM(Note)))-24)) WHERE SplitID = 2) FROM tblData) = 'none'
  AND ISNUMERIC((SELECT (SELECT Value FROM dbo.udf_Split(',',RIGHT(LTRIM(RTRIM(Note)),LEN(LTRIM(RTRIM(Note)))-24)) WHERE SplitID = 3) FROM tblData)) = 1



-- Run this to create the split function in your database

CREATE FUNCTION dbo.udf_Split (@Separator char(1), @String varchar(2000))
RETURNS table
AS
RETURN (
    WITH Pieces(SplitID, start, stop) AS (
      SELECT 1, 1, CHARINDEX(@Separator, @String)
      UNION ALL
      SELECT SplitID + 1, stop + 1, CHARINDEX(@Separator, @String, stop + 1)
      FROM Pieces
      WHERE stop > 0
    )
    SELECT SplitID,
      SUBSTRING(@String, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS Value
    FROM Pieces
  )
GO

Open in new window

0
 

Author Comment

by:Webboy2008
ID: 35731376
I really appreciate your help. I have one last question.

1. I would like to put the function and sql script together in one .sql file. Is this possible?
2. This is one time deal to update the script. and I would like to drop the function as well.
How can I do that?

Thanks,
0
 
LVL 21

Expert Comment

by:Alfred A.
ID: 35731460
Yes.  You can combine stored procs and functions in one sql script like for example:

USE [TestDB]

CREATE PROCEDURE Blah....

GO

CREATE FUNCTION Blah...

GO
0
 

Author Comment

by:Webboy2008
ID: 35731506
Trying to create function but have error
'Incorrect syntax near the keyword "WITH'.
LINE 17: INCORRECT SYNTAX NEAR ')'

CREATE FUNCTION dbo.udf_Split (@Separator char(1), @String varchar(2000))
RETURNS table
AS
RETURN (
    WITH Pieces(SplitID, start, stop) AS
    (
      SELECT 1, 1, CHARINDEX(@Separator, @String)
      UNION ALL
      SELECT SplitID + 1, stop + 1, CHARINDEX(@Separator, @String, stop + 1)
      FROM Pieces
      WHERE stop > 0
    )
    SELECT SplitID,
      SUBSTRING(@String, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS Value
    FROM Pieces
  )
0
 
LVL 21

Expert Comment

by:Alfred A.
ID: 35734889
This should have work.  What is SQL Server version anyway?  If it is below SQL Server 2005, then this will not work because CREATE FUNCTION is not available in SQL Server 2000 and SQL Server 7.0.  You need to create an alternative stored procedure out of the function if this is the case.

CREATE FUNCTION [dbo].[udf_Split] (@Separator char(1), @String varchar(2000))
RETURNS table
AS
RETURN (
    WITH Pieces(SplitID, start, stop) AS (
      SELECT 1, 1, CHARINDEX(@Separator, @String)
      UNION ALL
      SELECT SplitID + 1, stop + 1, CHARINDEX(@Separator, @String, stop + 1)
      FROM Pieces
      WHERE stop > 0
    )
    SELECT SplitID,
      SUBSTRING(@String, start, CASE WHEN stop > 0 THEN stop-start ELSE 2000 END) AS Value
    FROM Pieces
  )
0
 
LVL 21

Accepted Solution

by:
Alfred A. earned 2000 total points
ID: 35735037
Oh just a correction, CREATE FUNCTION is available in SQL Server 2000 but not SQL Server 7.0.

Anyway, this should work.  I use this in both SQL Server 2005 and 2008.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

850 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