January 14, 2011 at 7:39 am
Hello, I currently have a stored procedure written for Oracle that I need to port to SQL Server. Basically what I need to do is pass in a string of comma delimited column names that I will loop through and run some basic validity checks on and print out a message if any fail. So I need to parse through the list of columns check each one and print a message and move on the the next one.
Her e is an example of the string I will be parsing:
'ADDRESS','CITY_NAME','STATE_CODE','POSTAL_CODE','COUNTRY'
Any examples would be greatly appreciated.
Thank you,
David
Best Regards,
~David
January 14, 2011 at 7:54 am
You might want to have a look at the TallyTable article referenced in my signature. There's an example with a detailed description.
Based on that, follow the link in my signature regarding the delimited split function for a link to the latest version I know of.
January 14, 2011 at 8:42 am
Another option is to use the split function and load each column name as a record in a table. U would need to first create the split function. Code at bottom.
Here is some sample code to refference the split function and what it would return
--here is how u would quesry the function
select *
-- , you could put all your validity checks and output messages here as the below example states
, case when len(item)<5 then 'The column name is less than 5 chars' else 'Looks Good' end Error
from [fnSplitv2]('''ADDRESS'',''CITY_NAME'',''STATE_CODE'',''POSTAL_CODE'',''COUNTRY''',',')
--returns. The above checks to make sure their at least 5 chars long.
item Error
'ADDRESS'Looks Good
'CITY_NAME'Looks Good
'STATE_CODE'Looks Good
'POSTAL_CODE'Looks Good
'COUNTRY'Looks Good
--create split function
USE [NewBaby]
GO
/****** Object: UserDefinedFunction [dbo].[fnSplitv2] Script Date: 01/14/2011 10:34:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create FUNCTION [dbo].[fnSplitv2](
@sInputList VARCHAR(8000) -- List of delimited items
, @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))
BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
BEGIN
SELECT
@sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
IF LEN(@sItem) > 0
INSERT INTO @List SELECT @sItem
END
IF LEN(@sInputList) > 0
INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END
January 14, 2011 at 9:42 am
search around, there are a couple of ready solutions around
imo, Jeff Moden offers the best split function for strings with less than 4000 chars and Adam Machanic CLR split functions works best for big strings(varchar(max))
--
Thiago Dantas
@DantHimself
January 14, 2011 at 11:17 am
I have used some code from your referenced articles. Below is the code I have so far.
DECLARE
@Parameter VARCHAR(8000),
@tabName VARCHAR(100),
@colName VARCHAR(100),
@sqlStmt VARCHAR(4000),
@cnt INT
SET @Parameter = REPLICATE('OUTLET2_ADDRESS,CITY_NAME,STATE_CODE,POSTAL_CODE,COUNTRY',1)
SET @tabName = 'outlet'
SET NOCOUNT ON
--===== Create a table to store the results in
DECLARE @Elements TABLE
(
Number INT IDENTITY(1,1), --Order it appears in original string
Value VARCHAR(50)--The string value of the element
);
--===== Add start and end commas to the Parameter so we can handle -- single elements
SET @Parameter = ','+@Parameter +','
INSERT INTO @Elements
(Value)
SELECT SUBSTRING(@Parameter,N+1,CHARINDEX(',',@Parameter,N+1)-N-1)
FROM Tally
WHERE N < LEN(@Parameter)
AND SUBSTRING(@Parameter,N,1) = ',' --Notice how we find the comma
SET @cnt = @@ROWCOUNT
PRINT 'Row Count:' + CAST(@cnt AS VARCHAR)
I need to at this point iterate through each value in @elements, which are column names in the table specified in @tabName to see if it is null and print a message if it is.
Best Regards,
~David
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply