Link to home
Start Free TrialLog in
Avatar of Webboy2008
Webboy2008

asked on

asp.net sql


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
Avatar of anillucky31
anillucky31
Flag of India image

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

Avatar of Webboy2008
Webboy2008

ASKER

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

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.

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.
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

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,
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
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
  )
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
  )
ASKER CERTIFIED SOLUTION
Avatar of Alfred A.
Alfred A.
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial