Results 1 to 2 of 2
  1. #1
    Join Date
    May 2016

    Unanswered: List data of a Column with same Beginning

    Hi! i'm starting to work with Sql and i'm kind of lost.

    I have a colummn with thousands of ID's number.
    My boss requested me to list all ID's that begins with 0 and erase the 0.

    Ie: 0446785 to: 446785

    Can anyone help me with the command i need to execute in order to perform this action?

    Thanks in advance!

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    So you are storing a numeric value in aa string/text/char column... never a amart call to be honest

    To find all rows with a leading zero you could use
    The left function
    Or the substr function
    Or the like preedicate
    Or for overkill (and bonus marks) regular expressions
    Or you coul redesign the table and store the text as a number.
    Your call.
    Having found tge means to select rows with leading zeros yhen apply that where clause in an update query. Ferinstance :-
    update mytable set mycolumn = right (mycolumn,length (mycolumn) - 1)
    Where left(mycolumn,1) = '0'
    ...but youd have to run the query several times if say you had several rows with more than one leading zero.

    A far better solution is to change the underlying datatype of the column. But if you do decide to do this then do an imoact analysis BEFORE making changes. Ie what else needs changing eg screens, reports, queries. Cirrecting bad table design is more painful the longer you keave the problem to fester
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts