TSQL varchar 字符串操作
我有一个包含以下字符串的变量：AL，CA，TN，VA，NY
我无法控制该变量中的内容（来自报告服务）
我需要使其看起来像这样：'AL'，' CA'、'TN'、'VA'、'NY'
我该如何操作？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
我最终做了一些非常相似的事情，我想我会发布。 （不过，我会感谢 Mitch）
这会处理中间部分：
SET @StateList = REPLACE(@StateList, ',', ''',''')
然后引用边缘：
SET @WhereClause1 = @WhereClause1 + 'AND customerState IN (''' + @StateList + ''') '
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 + ''') '
对于更通用的答案，当您不知道输出到底是什么样子时，请使用 正则表达式。
这可以让您匹配 [AZ]{2} 之类的内容并将其替换为“$&”。
一位评论者认为这对于这项任务来说太过分了——同意，如果你能保证你总是会得到这样的字符串。 然而，其他人后来发现这些问题页面有类似但不确切的问题，因此其他选项会有所帮助。
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.
不用担心动态sql。
您需要将字符串转换为表格
所以
A、B、C、D
成为
值
A
乙
C
D
使用类似的函数
http://www.sqlusa.com/bestpractices/training/scripts/splitcommadelimited/< /a>
然后你可以使用 CROSS APPLY （这就像连接到一个表，但是是一个由函数创建的表）或者你可以将它放入一个表变量中并连接到该表
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
我想知道以下脚本是否在 SQL 中运行，而不是在 T-SQL
DECLARE @tblName varchar(30) 中运行
SET @tblName = CONVERT(VARCHAR(20),GETDATE(),112) + '表'
DECLARE @sql nvarchar(4000)
选择@sql=
'创建表“'+@tblName+'”
(
ID VARCHAR(15),
名称 VARCHAR(15)
)'
EXEC(@sql)
go
它会给您错误
Msg 170，Sev 15：第 1 行：'20090714Table' 附近的语法不正确。 [SQLSTATE 42000]
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]