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
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
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
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
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.
I just provided as near as possible working code. Without any specific information, I will stop at this point.
ASKER
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
ASKER
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,
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
USE [TestDB]
CREATE PROCEDURE Blah....
GO
CREATE FUNCTION Blah...
GO
ASKER
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
)
'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
)
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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