Couple of update statements

Update a column that strips leading zeros

Update a column that strips any trailing Alpha characters

Thanks,
Rob

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.

substring(col, patindex('%[^0]%', col) - 1, len(col)

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.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

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--" <jcel...@earthlink.net> wrote in message
news:1181080386....@q66g2000hsg.googlegroups.com...

"Erland Sommarskog" <esq...@sommarskog.se> wrote in message
news:Xns99472380...@127.0.0.1...

> would not be correct for Swedish text if there is ЕДЦ in it.

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

> would not be correct for Swedish text if there is ЕДЦ in it.

--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" <ro...@yahoo.com> wrote in message
news:_6mdnccFaaimhPvb...@comcast.com...

How might a statement be constructed to trim off trailing alpha characters
(same data) ?

Rob

"Uri Dimant" <ur...@iscar.co.il> wrote in message
news:%230k%23Y4$pHHA...@TK2MSFTNGP05.phx.gbl...

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

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:

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.

news:Xns99485CCA...@127.0.0.1...

http://beyondsql.blogspot.com/2007/06/dataphor-trimming-digits-and-letters.html