如何在表格中全局更新此表达式 [英] How Can I Do The Trick To Update This Expressions Globally In A Table
问题描述
嗨
我有一个表格存储表达式如'和（Rank in（1321619,1321620））和（评论如'％2016％'）'作为列值每条记录
我想全局更新表达式，如'...和（排名在（'1321619'，'1321620'））......'
来自'和（排名在（1321619,1321620））....
这些是表达式的一些值： -
Hi
I have a table that store expression like ' and (Rank in(1321619,1321620)) and (Comment like '%2016%')' as a column value per record
I want to update the expression globally like '...and (Rank in('1321619','1321620'))...'
from ' and (Rank in(1321619,1321620))....
These are some of expression values :-
and (GradutionYear =2016 and isnumeric(GradutionYear)=1 and GradutionYear<>0 ) and (Rank in(53322,53323,53324,53325,53327,53328,53329,53330,158676,158677,158678,158679))
and (GradutionYear =2017 and isnumeric(GradutionYear)=1 and GradutionYear<>0 ) and (Rank in('95022')) AND ((IsSubmitted = 1 OR IsImported = 1 ) OR (IsSubmitted = 1 ))
and (GradutionYear =2016 and isnumeric(GradutionYear)=1 and GradutionYear<>0 ) and (Rank in(53322,53323,53324,53325,53327,53328,53329,53330,158676,158677,158678,158679))
and ((FirstName + ' ' + LastName) like '%Emily Sofranko%')
and (GradutionYear =2015 and isnumeric(GradutionYear)=1 and GradutionYear<>0 ) and (Rank in(29975))
and (State like '%NY%') and (Rank in(100693,100694))
and (GradutionYear =2016 and isnumeric(GradutionYear)=1 and GradutionYear<>0 ) and (Rank in('137087','137088'))
and (Rank in(106688,116324,106689,106690,116325)) and (GradutionYear =2013 )
and (GradutionYear =2013 ) and (Rank in(491,492))
and ((FirstName + ' ' + LastName) like '%Frank Lipoli%')
and ((FirstName + ' ' + LastName) like '%franklin%')
我该怎么办？在全球范围内，非常感谢帮助！
谢谢！
How can i do this globally, Would appreciate the help!
Thanks!
推荐答案
这是一个非常专业的脚本，我正在考虑创建一个提示为问题似乎是通用的。
你需要两个SQL函数来解决你的问题。
This is a very specialized script and i am thinking of creating a Tip as the problem seems generic.
You need two SQL functions to address your issue.
CREATE FUNCTION dbo.SplitStrings_CTE
(
@List NVARCHAR(MAX),
@Delimiter NVARCHAR(255)
)
RETURNS @Items TABLE (Item NVARCHAR(4000))
WITH SCHEMABINDING
AS
BEGIN
DECLARE @ll INT = LEN(@List) + 1, @ld INT = LEN(@Delimiter);
WITH a AS
(
SELECT
[start] = 1,
[end] = COALESCE(NULLIF(CHARINDEX(@Delimiter,
@List, 1), 0), @ll),
[value] = SUBSTRING(@List, 1,
COALESCE(NULLIF(CHARINDEX(@Delimiter,
@List, 1), 0), @ll) - 1)
UNION ALL
SELECT
[start] = CONVERT(INT, [end]) + @ld,
[end] = COALESCE(NULLIF(CHARINDEX(@Delimiter,
@List, [end] + @ld), 0), @ll),
[value] = SUBSTRING(@List, [end] + @ld,
COALESCE(NULLIF(CHARINDEX(@Delimiter,
@List, [end] + @ld), 0), @ll)-[end]-@ld)
FROM a
WHERE [end] < @ll
)
INSERT @Items SELECT [value]
FROM a
WHERE LEN([value]) > 0
OPTION (MAXRECURSION 0);
RETURN;
END
GO
You can further read from here[^] for Split Function
CREATE FUNCTION dbo.SpecializedMerge
(
@text NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @ITEMS TABLE (ROWID INT IDENTITY, Item NVARCHAR(4000))
DECLARE @OutText NVARCHAR(MAX) = ''
DECLARE @ROWINDEX INT = 0
DECLARE @TMPSTRING NVARCHAR(MAX)
INSERT @ITEMS (item)
SELECT Item FROM dbo.SplitStrings_CTE (@text, N',');
SELECT @TMPSTRING = item
from @ITEMS
where ROWID = 1
select @TMPSTRING = reverse(left(REVERSE( @TMPSTRING), charindex('(',REVERSE( @TMPSTRING))-1))
UPDATE @ITEMS
SET item = '''' + item + ''''
WHERE isnumeric(item) > 0
UPDATE @ITEMS
SET item = REPLACE(item, @TMPSTRING, '''' + @TMPSTRING + '''')
where ROWID = 1
SELECT @TMPSTRING = I.item
from @ITEMS I
INNER JOIN (SELECT MAX(ROWID) ROWID FROM @ITEMS) T ON I.ROWID = T.ROWID
select @TMPSTRING = LEFT(@TMPSTRING, charindex(')',@TMPSTRING)-1)
UPDATE I
SET item = REPLACE(item, @TMPSTRING, '''' + @TMPSTRING + '''')
FROM @ITEMS I
INNER JOIN (SELECT MAX(ROWID) ROWID FROM @ITEMS) T ON I.ROWID = T.ROWID
SELECT @OutText = @OutText + COALESCE(item, '') + ','
FROM @ITEMS
RETURN @OutText;
END
GO
现在我们已经构建了所有工具，所以让我们使用它。
As now we have built all tools so let's use it.
DECLARE @TBL TABLE
(
EXPRESSION NVARCHAR(MAX)
)
INSERT INTO @TBL(EXPRESSION)
SELECT ' and (Rank in(1321619,1232323,123131)) and (Comment like ''%2016%'')'
UNION ALL
SELECT 'and (GradutionYear =2016 and isnumeric(GradutionYear)=1 and GradutionYear<>0 ) and (Rank in(53322,53323,53324,53325,53327,53328,53329,53330,158676,158677,158678,158679))'
SELECT * FROM @TBL
UPDATE @TBL
SEt EXPRESSION = dbo.SpecializedMerge(EXPRESSION)
SELECT * FROM @TBL
嗯，听起来像是在那里用文本字段替换字符串
umm, sounds like your replacing a string in a text field there
update mytable
set myfield = replace(myfield, 'like '' and (Rank in(1321619,1321620)) and (Comment like ''%2016%'')''', ' like ''...and (Rank in(''1321619'',''1321620''))...''
from '' and (Rank in(1321619,1321620)).... ')
或者更简单，没有所有的单引号转义。 ..
or to put it much simpler without all the single quote escaping...
update mytable
set myfield = replace(myfield, 'origtext', 'newtext')
上面的
将在该字段的每一列中用'newtext'替换'origtext'的任何实例，通常在有人拥有存储在sql server中的文档路径时使用，当文档被移动到其他地方时必须更新。
更新如下......
好的，所以你试图给它添加一些引号字符串的中间，除非每行都有exa文本同样（即这些数字总是为1321619,1232323,123131所以你可以用'1321619'，'1232323'，'123131'代替，或者总是将（1& 1替换为''1和'，'1并且这些模式在字符串中没有出现在其他地方）这对于正则表达式而言比SQL服务器更重要，我会对支持正则表达式的编程语言进行一些阅读，c＃适用于MSSQL Server。警告说，这是一个全新的大脑砰砰痛的世界。
above would replace any instance of 'origtext' with 'newtext' in every column for that field, usually used when someone has for example document paths stored in a sql server that have to be updated when the documents are moved somewhere else.
updated below ...
OK, so your trying to add some quotation marks to the middle of a string, unless every row has text that is exactly the same (i.e. the numbers are always going to be 1321619,1232323,123131 so you can do a replace with '1321619','1232323','123131' or is always going to be (1 & ,1 to be replaced with ('1 and ','1 and these patterns appear nowhere else in the string) this is more of a job for regular expressions than SQL server, I'd do some reading up on a programming language that supports regex, c# works well with MSSQL Server. Be warned, this is a whole new world of brain thumping pain.
update mytable
set myfield = replace(myfield, '(1', '(''1')
update mytable
set myfield = replace(myfield, ',1', ''',''1')
update mytable
set myfield = replace(myfield, '0)', '0'')')
你最糟糕的问题当然是在结束括号前面加上一个语音标记，因为我认为这个数字是最有可能改变，SQL不会像[0-9]那样用正则表达式表示任何数字。
以上2可能有效...如果其中任何一个模式出现在其中的任何其他地方，那么在备份第一cos上测试也可能会提高你的字符串
your worst gotcha here of course is putting a speech mark in front of the closing bracket, as that number I would think is the most likely to change and SQL won't do regex like [0-9] to indicate any number.
the above 2 may work... test on a backup first cos might also c**k up your strings if either of those patterns appear anywhere else in them
找到经过一段时间的脑力激荡......这对于''53322'以及53322这样的值来说是有效的
Found this after some brain storming ...This will work if for ''53322'' as well as values like 53322
begin
declare @a as varchar(max) = 'and (GradutionYear =2016 and isnumeric(GradutionYear)=1 and GradutionYear<>0 ) and (Rank in(''53322'',53323,53324,53325,53327,53328,53329,53330,158676,158677,158678,158679)) and (GradutionYear =2016 and isnumeric(GradutionYear)=1 and GradutionYear<>0 )'
declare @b as varchar(max)
declare @c as varchar(max)
declare @d as varchar(max)
declare @e as varchar(max)
declare @f as varchar(max)
declare @g as varchar(max)
select @b = substring(@a, patindex('%(rank in(%',@a), len(@a))
select @c = substring(@b, 0, patindex('%)%',@b) + 2)
select @d = replace(replace(@c, '(rank in(',''), ')','')
select @d
select @e = isnull(@e,'') + ',''' + replace(val,'''','') + '''' from dbo.split(@d, ',')
select @f = stuff(@e,1,1,'')
select @g = replace(@c, @d, @f)
select @g = replace(@a, @c, @g)
select @g
end
Values of the variables as the code flows :-
@a and (GradutionYear =2016 and isnumeric(GradutionYear)=1 and GradutionYear<>0 ) and (Rank in(53322,53323,53324,53325,53327,53328,53329,53330,158676,158677,158678,158679)) and (GradutionYear =2016 and isnumeric(GradutionYear)=1 and GradutionYear<>0 )
@b (Rank in(53322,53323,53324,53325,53327,53328,53329,53330,158676,158677,158678,158679)) and (GradutionYear =2016 and isnumeric(GradutionYear)=1 and GradutionYear<>0 )
@c (Rank in(53322,53323,53324,53325,53327,53328,53329,53330,158676,158677,158678,158679))
(1 row(s) affected)
@d 53322,53323,53324,53325,53327,53328,53329,53330,158676,158677,158678,158679
@e ,'53322','53323','53324','53325','53327','53328','53329','53330','158676','158677','158678','158679'
@f '53322','53323','53324','53325','53327','53328','53329','53330','158676','158677','158678','158679'
@g (Rank in('53322','53323','53324','53325','53327','53328','53329','53330','158676','158677','158678','158679'))
@g and (GradutionYear =2016 and isnumeric(GradutionYear)=1 and GradutionYear<>0 ) and (Rank in('53322','53323','53324','53325','53327','53328','53329','53330','158676','158677','158678','158679')) and (GradutionYear =2016 and isnumeric(GradutionYear)=1 and GradutionYear<>0 )
@g is required expression
Thanks
Thanks
这篇关于如何在表格中全局更新此表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
