4

I have a string like this:

'one, two, three'

How can I get it to look like this:

'one','two','three'

so I can use it in an IN clause?

xaisoft
  • 3,343
  • 8
  • 44
  • 72
  • 1
    Use [replace](https://msdn.microsoft.com/en-us/library/ms186862.aspx). Btw. you can't use strings in in-clause, you'll need also to create dynamic SQL then. – James Z May 10 '16 at 17:30
  • @JamesZ - I am not sure what you m mean by I can't use strings in in-clause. I have done something like this before: select * from table where column in ('one','two','three') and it works fine – xaisoft May 10 '16 at 17:32
  • You can do in clause that way, but you cannot use strings that have that in it. It will only look for that specific value, row with value `'one','two','three'`, not the 3 separate values – James Z May 10 '16 at 17:49
  • If you don't want to use dynamic sql you can convert the string to XML and then parse the XML into a table to join to. This won't work if you have non-valid XML characters in your input. – Hogan May 10 '16 at 17:55
  • 1
    If you're using stored procedures, you should actually use table valued parameters instead of passing comma separated strings – James Z May 10 '16 at 18:15
  • Be VERY careful here. This is highly likely to be vulnerable to sql injection. – Sean Lange May 10 '16 at 18:42
  • You may find some useful information [here](http://stackoverflow.com/a/43767/92546). – HABO May 10 '16 at 19:29

1 Answers1

1

Simply use the REPLACE method. Replace comma with the required string

DECLARE @TestData AS VARCHAR (200) = '''one, two, three''';
DECLARE @ReplacedData AS VARCHAR (200) = '';
SELECT @ReplacedData = REPLACE(@TestData, ', ', ''',''')

If you want to use the @TestData in the IN, you need to use the dynamic query like below:

DECLARE @SqlTest AS VARCHAR (MAX) = '';
SET @SqlTest = 'SELECT * FROM TestTable WHERE ColumnValue IN (' + @ReplacedData + ')'
EXEC (@SqlTest)
Arulkumar
  • 12,966
  • 14
  • 47
  • 68
  • I tried this, but my data just looks like this in the column: one, two, three. It does not have the back ticks, so it still produced the same string – xaisoft May 10 '16 at 17:35
  • I updated my post, it must have put it like that when editing. It just has single quotes in the original string – xaisoft May 10 '16 at 17:36
  • I noticed you put ' ' 'one,two,three' ' ', but when if my string just is 'one,two,three' – xaisoft May 10 '16 at 17:47
  • you have to remove the parentheses sp_executesql @SqlTest – Jande May 10 '16 at 17:49
  • @Arulkumar - This string '''one, two, three''' seems to work fine, but my string only has one quote surrounding the whole thing – xaisoft May 10 '16 at 17:51
  • Ok, correct me if I am wrong, but do I need the 3 quotes because that is what is used as a pattern to replace it with one quote? does that make sense – xaisoft May 10 '16 at 17:55
  • If you want to retain a `'` in the string you need to use two single quotes, it is like escaping character. – Arulkumar May 10 '16 at 17:58
  • `DECLARE @A AS VARCHAR(100) = 'one, two, three'; SELECT @A; DECLARE @B AS VARCHAR(100) = '''one, two, three'''; SELECT @B;` In more detail if I want to retain the `'` I use a escape character – Arulkumar May 10 '16 at 18:08
  • @dejan87 I'm not replacing comma, replacing comma to quotes around the comma. From `, ` to `','` – Arulkumar May 10 '16 at 18:12