How do I programmatically add an apostrophe before and after a string and add a comma inbetween?

Question

I get lists in Outlook, Word or Excel of hundreds of strings like so: 7864750A 7888801BZ 5189748C 48982572E 6936001F 7096235FG 3218833H etc. Currently I run a stored procedure on each of these, in one-by-one fashion.

How could I programmatically add an apostrophe before and after a string and add a comma inbetween, like so: ('7864750A','7888801BZ','5189748C','48982572E','936001F','7096235FG','3218833H')?

I want to change my stored procedure to use IN (' ',' ',' ') and run all of them at once?

Answer 1

I would just use find and replace all instances of " " with " ',' " , and then add " (' " at the beginning and " ') " at the end.

Answer 2

You could write a table-valued UDF that takes the strings as input and returns the items in separate rows of a table. Something like this (not the most efficient thing in the world...):

CREATE FUNCTION [dbo].[SplitMyValues]
(
    @Input VARCHAR(MAX)
)
RETURNS @Results TABLE
(
    Data VARCHAR(MAX)
)
AS
BEGIN

    DECLARE @Where   INT
    DECLARE @Length  INT
    DECLARE @Next    INT

    SET @Where  = 0
    SET @Length = LEN(@Input)

    WHILE (@Where < @Length)
    BEGIN
     SET @Next = CHARINDEX(' ', @Input, @Where)

     IF (@Next > 0)
     BEGIN
      INSERT INTO @Results VALUES (SUBSTRING(@Input, @Where, @Next - @Where))
      SET @Where = @Next + 1
     END
     ELSE
     BEGIN
      INSERT INTO @Results VALUES (SUBSTRING(@Input, @Where, LEN(@Input)))
      SET @Where = @Length
     END
    END

    RETURN
END

To see the output, you can just run this:

SELECT *
FROM
    dbo.SplitMyValues('7864750A 7888801BZ 5189748C 3218833H')

Then you can just do a join or a subquery in the WHERE clause of an UPDATE or SELECT, like:

SELECT *
FROM
   SomeTable S
   INNER JOIN dbo.SplitMyValues('7864750A 7888801BZ 5189748C 3218833H') X
     ON X.Data = S.WhateverColumn

Answer 3

DECLARE @STRING varchar(max) 
SELECT @STRING = '7864750A 7888801BZ 5189748C 48982572E 6936001F 7096235FG 3218833H'

SELECT '(''' + REPLACE(@STRING,' ',''',''') + ''')'

gives this result:

('7864750A','7888801BZ','5189748C','48982572E','6936001F','7096235FG','3218833H')

Answer 4

One trick I use from time to time on excel data...

Supposing there are several rows of data in columns A and B starting on the 2nd row. I'll go to cell C2 and place a string formula in there.

="INSERT INTO MyTable(X, Y) SELECT '" & A2 & "', '" & B2 & "'"

which evaluates to

INSERT INTO MyTable(X, Y) SELECT 'AValue', 'BValue'

Then I copy and paste that down the C column... relative cell references take care of the rest.

PS: Watch out for nulls!