How Can I Do The Trick To Update This Expressions Globally In A Table

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

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 :-

SQL

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!

Posted 5-Jan-16 20:50pm

Gaurav_Chaudhary

Updated 6-Jan-16 0:04am

v3

Add a Solution

Comments

Kornfeld Eliyahu Peter 6-Jan-16 2:58am

It is unclear - to me at least - what do you mean by 'globally'...

[no name] 6-Jan-16 3:01am

Globally means whole table data - thanks For prompt reply

Kornfeld Eliyahu Peter 6-Jan-16 3:02am

Do you mean to update all the row i the table? What the problem? Remove any WHERE clause and it will do it...

Kornfeld Eliyahu Peter 6-Jan-16 3:16am

And?
UPDATE tabel SET field = REPLACE(...)

Tomas Takac 6-Jan-16 2:59am

It's a string, right? So UPDATE with REPLACE should do that. Where's the catch?

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

_Asif_ · Accepted Answer · 2016-01-05T21:56:00

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.

SQL

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

SQL

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.

SQL

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

TenmanS14 · Accepted Answer · 2016-01-05T22:21:00

umm, sounds like your replacing a string in a text field there

SQL

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...

SQL

update mytable
set myfield = replace(myfield, 'origtext', 'newtext')

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.

SQL

update mytable
set myfield = replace(myfield, '(1', '(''1')

update mytable
set myfield = replace(myfield, ',1', ''',''1')

update mytable
set myfield = replace(myfield, '0)', '0'')')

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

Gaurav_Chaudhary · Accepted Answer · 2016-01-07T02:04:00

Found this after some brain storming ...This will work if for ''53322'' as well as values like 53322

SQL

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

How Can I Do The Trick To Update This Expressions Globally In A Table

3 solutions

Solution 1

Solution 2

Solution 4

Add your solution here

Preview 0