How to get a list of numbers into a table

1

Hi,
I am often give a list of numbers like this (only it is usually 10000 or more) sometimes in an email:
1254
s4585d
5548
52535P
6565

what I usually do is put them in excel and then import them in a table. I then run code to put them in this form:
5548 --> ' 5548',
So that I can use it in the SELECT statement with and IN statement.
Is there an easier way to get these in a table?
Thank you

2

If you are getting the list in an e-mail, importing via Excel might be as good as any other option. As for using those to filter a where clause, you might do the following:

SELECT col1, col2 -- etc
FROM
	YourDataTable a
WHERE EXISTS
	(
		SELECT *
		FROM
			YourImportedTable b
		WHERE
			b.ColName = a.ColName
	);

If you want to just use the list in an IN clause, you can copy the data to an SSMS window and use Find and Replace as shown in the screenshot below

The result should look like the following:

1 Like
3

Thanks that will work.
Thank you

4

A friend came up with this. Note it is not optimized but it works

DECLARE @Delimiter char(1) = char(10)
DECLARE @List varchar(max) =  -- Paste in a list of accts between the single quotes.
'
XXXXXX0060XXXXXX
XXXXXX0074XXXXXX
XXXXXX0080XXXXXX
XXXXXX0083XXXXXX
XXXXXX0271XXXXXX
XXXXXX5840XXXXXX
XXXXXX5907XXXXXX
XXXXXX5910XXXXXX
'
IF OBJECT_ID (N'tempdb..#AcctList', N'U') IS NOT NULL DROP TABLE #AcctList; 
create table #AcctList (Acct varchar(30))
insert into #AcctList select * from udf_string_to_table(@List,@Delimiter)
GO

CREATE FUNCTION [dbo].[udf_string_to_table]
(
    @string VARCHAR(MAX),
    @delimiter CHAR(1)
)
RETURNS @output TABLE(
    data VARCHAR(256)
)
BEGIN
    DECLARE @start INT, @end INT
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)

    WHILE @start < LEN(@string) + 1 BEGIN
        IF @end = 0 
            SET @end = LEN(@string) + 1

        INSERT INTO @output (data) 
        VALUES(SUBSTRING(@string, @start, @end - @start))
        SET @start = @end + 1
        SET @end = CHARINDEX(@delimiter, @string, @start)
    END
    RETURN
END
GO
5

Thank you
This is interesting too

6

It's likely even easier to just save the list as a file and do a BULK INSERT. That way, there's not even a copy'n'paste involved. An even better way would be to have then send you the file as a CSV file and that would eliminate yet another manual step of having to separate the list from anything else they may have sent you.