InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 14:57:49 GMT20http://www.sqlservercentral.com/Forums/Topic1424814-1292-1.aspx[quote][b]mickyT (2/27/2013)[/b][hr][quote]Why? Please don't say "for performance reasons" until you've actually tested it. I tested all of the solutions offered so far and there's virtually no difference. Try it yourself. Here's a million row test table...[/quote]Sorry Jeff, I wasn't suggesting them for performance reasons, just other ways of doing it. I found the method the OP put up originally difficult to decipher at first glance.[/quote] Ah. Got it. Thanks for the feedback.Wed, 27 Feb 2013 23:10:26 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1424814-1292-1.aspx[quote]Why? Please don't say "for performance reasons" until you've actually tested it. I tested all of the solutions offered so far and there's virtually no difference. Try it yourself. Here's a million row test table...[/quote]Sorry Jeff, I wasn't suggesting them for performance reasons, just other ways of doing it. I found the method the OP put up originally difficult to decipher at first glance.Wed, 27 Feb 2013 22:43:05 GMTmickyThttp://www.sqlservercentral.com/Forums/Topic1424814-1292-1.aspx[quote][b]mickyT (2/27/2013)[/b][hr]However if you only have numbers in the string, I would probably go for a statement more like[code="sql"]UPDATE table.StatementsSET field03 = CAST(CAST(field03 as bigint) as varchar(20))WHERE field03 like '0%'[/code]Otherwise you could do[code="sql"]UPDATE table.StatementsSET field03 = SUBSTRING(field03,PATINDEX('%[^0]%',field03),20)WHERE field03 like '0%'[/code][/quote]Why? Please don't say "for performance reasons" until you've actually tested it. I tested all of the solutions offered so far and there's virtually no difference. Try it yourself. Here's a million row test table...[code="sql"]--===== Create and populate a large test table on-the-fly SELECT TOP 1000000 Field03 = RIGHT('0000000000'+CAST(ABS(CHECKSUM(NEWID()))%2147483647+1 AS VARCHAR(10)),10) INTO #TestTable FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2;[/code]Wed, 27 Feb 2013 21:52:17 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1424814-1292-1.aspx[quote][b]Scott Milburn (2/27/2013)[/b][hr]I have four tables that have a varchar data type which gets imports or daily user updates and this field is an account number field which always has a length of 10, including leading zeros. My current code uses:UPDATE table.StatementsSET field03 = REPLACE(LTRIM(REPLACE(field03, '0', ' ')), ' ', '0')The issue is that this runs against all four tables and takes around 5 minutes. After the code runs, I see that it touched every record, which I don't want. I just want the code to look at the rows that this field needs to be changed on and disregard the previously trimmed rows.I know I need some kind of IF statement to accomplish this, but not sure how.TIA, Scott[/quote]If Field03 is for account number, I'm thinking that you probably have an index or two on it. The real problems may be that 1) you're updating both the table and the index(es) and 2) you're fragging the hell out of the index(es).I'd recommend dropping the index(es), doing the update, then re-add te index(es). You're likely going to have to rebuild them after this, anyway.Wed, 27 Feb 2013 21:34:51 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1424814-1292-1.aspxTry [code="sql"]UPDATE table.StatementsSET field03 = REPLACE(LTRIM(REPLACE(field03, '0', ' ')), ' ', '0')WHERE field03 like '0%'[/code]This will only try to update where field03 starts with a 0. An index on this would helpHowever if you only have numbers in the string, I would probably go for a statement more like[code="sql"]UPDATE table.StatementsSET field03 = CAST(CAST(field03 as bigint) as varchar(20))WHERE field03 like '0%'[/code]Otherwise you could do[code="sql"]UPDATE table.StatementsSET field03 = SUBSTRING(field03,PATINDEX('%[^0]%',field03),20)WHERE field03 like '0%'[/code]Wed, 27 Feb 2013 19:02:47 GMTmickyThttp://www.sqlservercentral.com/Forums/Topic1424814-1292-1.aspxI have four tables that have a varchar data type which gets imports or daily user updates and this field is an account number field which always has a length of 10, including leading zeros. My current code uses:UPDATE table.StatementsSET field03 = REPLACE(LTRIM(REPLACE(field03, '0', ' ')), ' ', '0')The issue is that this runs against all four tables and takes around 5 minutes. After the code runs, I see that it touched every record, which I don't want. I just want the code to look at the rows that this field needs to be changed on and disregard the previously trimmed rows.I know I need some kind of IF statement to accomplish this, but not sure how.TIA, ScottWed, 27 Feb 2013 17:04:28 GMTScott Milburn