Whole list from csv
-
I get returned a table where objectname can be a list of objects separated by a comma.
E.g.
Objectname
A
B,C,D
E,F
G
G,H,OKHow can I return 1 list (table) that has all of these objects. So that I can say:
Select name from sysobjects where name in ..... It's this bit I can't fathomDarryl Wilson
darrylw99@hotmail.com -
http://www.sqlservercentral.com/blogs/spaghettidba/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
DECLARE @t table(vals varchar(100));
INSERT @t ( vals )
VALUES ( 'A' )
, ( 'B,C,D' )
, ( 'E,F' )
, ( 'G' )
, ( 'G,H,OK' );
SELECT Substring(vals, sv.number,
CharIndex(','
, vals + ','
, sv.number + 1
) - sv.number
) SplitVals
FROM
master.dbo.spt_values sv
CROSS JOIN
@t t
WHERE
sv.type='P'
AND
number BETWEEN 1 AND Len(vals)
AND
Substring(','+vals, sv.number, 1)=',';
-
Just so I understand correctly, you have a comma separated list in a flat file and you want to select out of it?
If this is correct, you would want to use something like SSIS to import the data into SQL and then you can select out of it.
If you already have it in a table and you need to split it out, something like this should work (as long as your object names are always 1 character):DECLARE @STR VARCHAR(100) = 'bird,cat,dog';
DECLARE @counter INT = 0;
DECLARE @tmpTable TABLE ( [word] CHAR(100) );
DECLARE @strNoComma VARCHAR(100);
WHILE @counter < LEN(@str) + 2
BEGIN
IF EXISTS ( SELECT
1
FROM
@tmpTable
WHERE
SUBSTRING(@str, @counter, 1) NOT LIKE ',' )
BEGIN
SELECT
@strNoComma = @strNoComma + SUBSTRING(@str, @counter,
1);
END;
ELSE
BEGIN
INSERT INTO @tmpTable
( [word] )
VALUES
( @strNoComma );
SELECT
@strNoComma = '';
END;
SELECT
@counter = @counter + 1;
END;
INSERT INTO @tmpTable
( [word] )
VALUES
( @strNoComma );
SELECT
[@tmpTable].[word]
FROM
@tmpTable
WHERE
[@tmpTable].[word] NOT LIKE ',';
I imagine this isn't the most efficient way to do this, but it works. You'd need to do a few little tweaks to get it to work on a table, but should be a good start, no? May need to increase the VARCHAR's as well if you have some really wide results.The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system. -
If I'm reading your question correctly, this looks like a string splitting exercise. The function in Jeff Moden's article at http://www.sqlservercentral.com/articles/Tally+Table/72993/ will be a great asset here. Once you create the DelimitedSplit8K function, here's the DDL to create and populate your table with the values in your original post.
IF OBJECT_ID('tempdb.dbo.#objects', 'u') IS NOT NULL DROP TABLE #objects;
CREATE TABLE #objects (
name varchar(64));INSERT INTO #objects(name)
VALUES('A'),
('B,C,D'),
('E,F'),
('G'),
('G,H,OK');The query then becomes pretty straightforward.
SELECT s.Item
FROM #objects o
CROSS APPLY dbo.DelimitedSplit8K(o.name, ',') s
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply