Sql Server Programming :: Couple of update statements


	Couple of update statements How would you wrte the following update statements ? Update a column that strips leading zeros Update a column that strips any trailing Alpha characters Thanks, Rob >> How would you write the following update statements ? << Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. Why do you have such strings in the Schema when you can write CHECK() constraints that would prevent this in the first place?? That is one of the basic ideas of RDBMS. -----------------------------------------------Reply----------------------------------------------- Rob (r@yahoo.com) writes: > How would you wrte the following update statements ? > Update a column that strips leading zeros substring(col, patindex('%[^0]%', col) - 1, len(col) > Update a column that strips any trailing Alpha characters substring(col, 1, len (col) - (patindex('%[^A-z]%', reverse(col) - 1))) Both these are untested, and there may be one-off errors. Also, the exact expression for trailing alpha is collation dependent. The above would not be correct for Swedish text if there is in it. -- Erland Sommarskog, SQL Server MVP, esq@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books... Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx -----------------------------------------------Reply----------------------------------------------- Regarding... >Why do you have such strings in the Schema when you can write CHECK() >constraints that would prevent this in the first place?? That is one >of the basic ideas of RDBMS That is a good question for the shipping company that has exported this data into a spreadsheet from their system and forwarded it to us. Regarding "Please post DDL" , I thought it was somewhat of a generic request, but I do agree with you. "--CELKO--" <jcelko@earthlink.net> wrote in message news:1181080386.858338.67890@q66g2000hsg.googlegroups.com... - Hide quoted text - >>> How would you write the following update statements ? << > Please post DDL, so that people do not have to guess what the keys, > constraints, Declarative Referential Integrity, data types, etc. in > your schema are. Sample data is also a good idea, along with clear > specifications. It is very hard to debug code when you do not let us > see it. > Why do you have such strings in the Schema when you can write CHECK() > constraints that would prevent this in the first place?? That is one > of the basic ideas of RDBMS. Thank you very much, I will give them a try... "Erland Sommarskog" <esq@sommarskog.se> wrote in message news:Xns99472380F93DYazorman@127.0.0.1... - Hide quoted text - > Rob (r@yahoo.com) writes: >> How would you wrte the following update statements ? >> Update a column that strips leading zeros > substring(col, patindex('%[^0]%', col) - 1, len(col) >> Update a column that strips any trailing Alpha characters > substring(col, 1, len (col) - (patindex('%[^A-z]%', reverse(col) - 1))) > Both these are untested, and there may be one-off errors. Also, the > exact expression for trailing alpha is collation dependent. The above > would not be correct for Swedish text if there is in it. > -- > Erland Sommarskog, SQL Server MVP, esq@sommarskog.se > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books... > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Hi, I did try the following (to get rid of the leading zeros), but it does not appear to be working... maybe I did something wrong... CREATE TABLE [dbo].[LTLData]( [ProNum] [nvarchar](255) COLLATE Latin1_General_BIN NULL ) ON [PRIMARY] insert into LTLData (ProNum) Values ('00234') insert into LTLData (ProNum) Values ('030234BD') insert into LTLData (ProNum) Values ('33030234BD') insert into LTLData (ProNum) Values ('0233030234BD') insert into LTLData (ProNum) Values ('0000234BD') update LTLData set ProNum =substring(ProNum, patindex('%[^0]%', ProNum) - 1, len(ProNum)) Thanks, Rob "Erland Sommarskog" <esq@sommarskog.se> wrote in message news:Xns99472380F93DYazorman@127.0.0.1... - Hide quoted text - > Rob (r@yahoo.com) writes: >> How would you wrte the following update statements ? >> Update a column that strips leading zeros > substring(col, patindex('%[^0]%', col) - 1, len(col) >> Update a column that strips any trailing Alpha characters > substring(col, 1, len (col) - (patindex('%[^A-z]%', reverse(col) - 1))) > Both these are untested, and there may be one-off errors. Also, the > exact expression for trailing alpha is collation dependent. The above > would not be correct for Swedish text if there is in it. > -- > Erland Sommarskog, SQL Server MVP, esq@sommarskog.se > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books... > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Rob If you use SQL Server 2005 that is a perfect chance using CLR to do the job --T-SQL while exists (select * from LTLData where left(ProNum,1) like '0%') update LTLData set ProNum = stuff(ProNum, patindex('0%', ProNum), 1, space(0)) where left(ProNum,1) like '0%' "Rob" <r@yahoo.com> wrote in message news:_6mdnccFaaimhPvbnZ2dnUVZ_uuqnZ2d@comcast.com... - Hide quoted text - > Hi, > I did try the following (to get rid of the leading zeros), but it does not > appear to be working... maybe I did something wrong... > CREATE TABLE [dbo].[LTLData]( > [ProNum] [nvarchar](255) COLLATE Latin1_General_BIN NULL > ) ON [PRIMARY] > insert into LTLData (ProNum) Values ('00234') > insert into LTLData (ProNum) Values ('030234BD') > insert into LTLData (ProNum) Values ('33030234BD') > insert into LTLData (ProNum) Values ('0233030234BD') > insert into LTLData (ProNum) Values ('0000234BD') > update LTLData set ProNum =substring(ProNum, patindex('%[^0]%', ProNum) - > 1, len(ProNum)) > Thanks, > Rob > "Erland Sommarskog" <esq@sommarskog.se> wrote in message > news:Xns99472380F93DYazorman@127.0.0.1... >> Rob (r@yahoo.com) writes: >>> How would you wrte the following update statements ? >>> Update a column that strips leading zeros >> substring(col, patindex('%[^0]%', col) - 1, len(col) >>> Update a column that strips any trailing Alpha characters >> substring(col, 1, len (col) - (patindex('%[^A-z]%', reverse(col) - 1))) >> Both these are untested, and there may be one-off errors. Also, the >> exact expression for trailing alpha is collation dependent. The above >> would not be correct for Swedish text if there is in it. >> -- >> Erland Sommarskog, SQL Server MVP, esq@sommarskog.se >> Books Online for SQL Server 2005 at >> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books... >> Books Online for SQL Server 2000 at >> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Thanks Uri, How might a statement be constructed to trim off trailing alpha characters (same data) ? CREATE TABLE [dbo].[LTLData]( [ProNum] [nvarchar](255) COLLATE Latin1_General_BIN NULL ) ON [PRIMARY] insert into LTLData (ProNum) Values ('00234') insert into LTLData (ProNum) Values ('030234BD') insert into LTLData (ProNum) Values ('33030234BD') insert into LTLData (ProNum) Values ('0233030234BD') insert into LTLData (ProNum) Values ('0000234BD') Rob "Uri Dimant" <u@iscar.co.il> wrote in message news:%230k%23Y4$pHHA.3508@TK2MSFTNGP05.phx.gbl... - Hide quoted text - > Rob > If you use SQL Server 2005 that is a perfect chance using CLR to do the > job > --T-SQL > while exists (select * from LTLData where left(ProNum,1) like '0%') > update LTLData > set ProNum = stuff(ProNum, patindex('0%', ProNum), 1, space(0)) > where left(ProNum,1) like '0%' > "Rob" <r@yahoo.com> wrote in message > news:_6mdnccFaaimhPvbnZ2dnUVZ_uuqnZ2d@comcast.com... >> Hi, >> I did try the following (to get rid of the leading zeros), but it does >> not appear to be working... maybe I did something wrong... >> CREATE TABLE [dbo].[LTLData]( >> [ProNum] [nvarchar](255) COLLATE Latin1_General_BIN NULL >> ) ON [PRIMARY] >> insert into LTLData (ProNum) Values ('00234') >> insert into LTLData (ProNum) Values ('030234BD') >> insert into LTLData (ProNum) Values ('33030234BD') >> insert into LTLData (ProNum) Values ('0233030234BD') >> insert into LTLData (ProNum) Values ('0000234BD') >> update LTLData set ProNum =substring(ProNum, patindex('%[^0]%', ProNum) - >> 1, len(ProNum)) >> Thanks, >> Rob >> "Erland Sommarskog" <esq@sommarskog.se> wrote in message >> news:Xns99472380F93DYazorman@127.0.0.1... >>> Rob (r@yahoo.com) writes: >>>> How would you wrte the following update statements ? >>>> Update a column that strips leading zeros >>> substring(col, patindex('%[^0]%', col) - 1, len(col) >>>> Update a column that strips any trailing Alpha characters >>> substring(col, 1, len (col) - (patindex('%[^A-z]%', reverse(col) - >>> 1))) >>> Both these are untested, and there may be one-off errors. Also, the >>> exact expression for trailing alpha is collation dependent. The above >>> would not be correct for Swedish text if there is in it. >>> -- >>> Erland Sommarskog, SQL Server MVP, esq@sommarskog.se >>> Books Online for SQL Server 2005 at >>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books... >>> Books Online for SQL Server 2000 at >>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Uri, A question about this... I see that the statement contains a "while" thus I assume it is some kind of "loop". For my purpose, it is fine, however, knowing the disdain some folks have for cursors... where does CLR stand ? In other words, which would be better to use.... CLR or a cursor ? Also, what is a good source for learning how to use CLR within SQL2005 ? Thanks, Rob "Uri Dimant" <u@iscar.co.il> wrote in message news:%230k%23Y4$pHHA.3508@TK2MSFTNGP05.phx.gbl... - Hide quoted text - > Rob > If you use SQL Server 2005 that is a perfect chance using CLR to do the > job > --T-SQL > while exists (select * from LTLData where left(ProNum,1) like '0%') > update LTLData > set ProNum = stuff(ProNum, patindex('0%', ProNum), 1, space(0)) > where left(ProNum,1) like '0%' > "Rob" <r@yahoo.com> wrote in message > news:_6mdnccFaaimhPvbnZ2dnUVZ_uuqnZ2d@comcast.com... >> Hi, >> I did try the following (to get rid of the leading zeros), but it does >> not appear to be working... maybe I did something wrong... >> CREATE TABLE [dbo].[LTLData]( >> [ProNum] [nvarchar](255) COLLATE Latin1_General_BIN NULL >> ) ON [PRIMARY] >> insert into LTLData (ProNum) Values ('00234') >> insert into LTLData (ProNum) Values ('030234BD') >> insert into LTLData (ProNum) Values ('33030234BD') >> insert into LTLData (ProNum) Values ('0233030234BD') >> insert into LTLData (ProNum) Values ('0000234BD') >> update LTLData set ProNum =substring(ProNum, patindex('%[^0]%', ProNum) - >> 1, len(ProNum)) >> Thanks, >> Rob >> "Erland Sommarskog" <esq@sommarskog.se> wrote in message >> news:Xns99472380F93DYazorman@127.0.0.1... >>> Rob (r@yahoo.com) writes: >>>> How would you wrte the following update statements ? >>>> Update a column that strips leading zeros >>> substring(col, patindex('%[^0]%', col) - 1, len(col) >>>> Update a column that strips any trailing Alpha characters >>> substring(col, 1, len (col) - (patindex('%[^A-z]%', reverse(col) - >>> 1))) >>> Both these are untested, and there may be one-off errors. Also, the >>> exact expression for trailing alpha is collation dependent. The above >>> would not be correct for Swedish text if there is in it. >>> -- >>> Erland Sommarskog, SQL Server MVP, esq@sommarskog.se >>> Books Online for SQL Server 2005 at >>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books... >>> Books Online for SQL Server 2000 at >>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Rob (r@yahoo.com) writes: > I did try the following (to get rid of the leading zeros), but it does not > appear to be working... maybe I did something wrong... I did say they were sketches, and I hope you would be able to work with them as a start. If you look at the result from the first query: > update LTLData set ProNum =substring(ProNum, patindex('%[^0]%', ProNum) - > 1, len(ProNum)) We see that one 0 is retained. It appears that dropping the -1 resolves the problem. (Patindex is quite confusing, so I was on a dare when I posted without testing. But without test data.... The other query I posted gives an error, because of a misplaced right parenthesis. The correct query reads: substring(ProNum, 1, len(ProNum) - (patindex('%[^A-z]%', reverse(ProNum)) - 1)) And when I test with the data you posted, it appears to give the desired result. -- Erland Sommarskog, SQL Server MVP, esq@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books... Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx -----------------------------------------------Reply----------------------------------------------- Thank You Very Much ! "Erland Sommarskog" <esq@sommarskog.se> wrote in message news:Xns99485CCAF5BCYazorman@127.0.0.1... - Hide quoted text - > Rob (r@yahoo.com) writes: >> I did try the following (to get rid of the leading zeros), but it does >> not >> appear to be working... maybe I did something wrong... > I did say they were sketches, and I hope you would be able to work > with them as a start. If you look at the result from the first query: >> update LTLData set ProNum =substring(ProNum, patindex('%[^0]%', ProNum) - >> 1, len(ProNum)) > We see that one 0 is retained. It appears that dropping the -1 resolves > the problem. (Patindex is quite confusing, so I was on a dare when I > posted without testing. But without test data.... > The other query I posted gives an error, because of a misplaced right > parenthesis. The correct query reads: > substring(ProNum, 1, > len(ProNum) - (patindex('%[^A-z]%', reverse(ProNum)) - 1)) > And when I test with the data you posted, it appears to give the desired > result. > -- > Erland Sommarskog, SQL Server MVP, esq@sommarskog.se > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books... > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx