Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

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

Thank you very much, I will give them a try...

"Erland Sommarskog" <esq@sommarskog.se> wrote in message

news:Xns99472380F93DYazorman@127.0.0.1...

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

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

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

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

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc