TSQL varchar string manipulation

Question

I have a variable which contains the following string: AL,CA,TN,VA,NY

I have no control over what I get in that variable (comes from reporting services)

I need to make it look like this: 'AL','CA','TN','VA','NY'

How do I do this?

Answer 1

I want to know y does the following script run in SQL and not in T-SQL

---

DECLARE @tblName varchar(30) SET @tblName = CONVERT(VARCHAR(20),GETDATE(),112) + 'Table'

DECLARE @sql nvarchar(4000) SELECT @sql = 'CREATE TABLE "' + @tblName + '" ( ID VARCHAR(15), Name VARCHAR(15) )'

EXEC(@sql)

go

it gives you the error

Msg 170, Sev 15: Line 1: Incorrect syntax near '20090714Table'. [SQLSTATE 42000]

Answer 2

Don't bother with dynamic sql.

You need to convert the string to a table so A,B,C,D

becomes

Value A B C D

using a function like http://www.sqlusa.com/bestpractices/training/scripts/splitcommadelimited/

then you can use CROSS APPLY (which is like joining to a table, but a table created by a function) or you can just put it in a table variable and join to that

Answer 3

For a more generic answer, when you don't know what your output will look like exactly, use regular expressions.

This would let you you match on something like [A-Z]{2} and replace it with '$&'.

A commenter suggested this is overkill for this task - agreed, if you can guarantee you will always get a string like that. However, other people find these question pages later with similar, but not exact, problems, so other options are helpful to have.

Answer 4

I ended up doing something very similar that I thought I'd post. (I'll give credit to Mitch however)

This takes care of the middle:

SET @StateList = REPLACE(@StateList, ',', ''',''')

Then quote the edges:

SET @WhereClause1 = @WhereClause1 + 'AND customerState IN (''' + @StateList + ''') '

Answer 5

declare @x varchar(50) = 'AL,CA,TN,VA,NY'

select '''' + REPLACE(@x, ',', ''',''') + ''''