Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2013
    Posts
    3

    Unanswered: A SQL question - Remove leading zeros

    Hi

    I'm new to this forum and I'm not sure if I've come to the right place but I'll ask anyway.

    I have the following SQL statement and I want to remove the leading zeros from the PR_Sales.InvoiceNumber column.

    SELECT PR_Sales.InvoiceDate, PR_Sales.InvoiceNumber, PR_Sales.TotalLines+PR_Sales.TotalTax-(PR_Sales.TotalCredits+PR_Sales.TotalDeposits+PR_S ales.TotalPaid), PR_Customers.CardIdentification, PR_Status.Description
    FROM `C:\AccountingPower\PowerReports\MYOB`.PR_Customer s PR_Customers, `C:\AccountingPower\PowerReports\MYOB`.PR_Sales PR_Sales, `C:\AccountingPower\PowerReports\MYOB`.PR_Status PR_Status
    WHERE PR_Status.StatusID = PR_Sales.InvoiceStatusID AND PR_Sales.CardRecordID = PR_Customers.CardRecordID AND ((PR_Status.Description='Open'))

    Is anyone able to help me with this please?

    Kind Regards

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    does the sales invoice number have any alpha character or is it only numeric
    if only numeric then you could explicitly convert it to, say an integer using CINT(mycolumnname) as NyNumericValue

    however it seems a little pointless to me if you have published an invoice number with leading zeros then that should be the reference used
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jul 2013
    Posts
    3
    Thanks for your reply. The sales invoice number is definitely only numeric. I'm sending this query to a csv file which is being sent to a bank. They have asked me to remove the leading zeros because somehow it's interfering with their software that they are sucking it into.
    Unfortunately I'm a bit of a novice when it comes to SQL. Could you copy my SELECT statement and insert your suggested commands please so I can see exactly how it goes. Thanks.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Code:
    , PR_Sales.InvoiceNumber,
    becomes
    Code:
    , CINT(PR_Sales.InvoiceNumber) as InvNo,
    you may need to beef this up if there is a possibility that the invoice number could be non numeric of contain NULLS
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    or infoirm the bank that its an alphanumeric colum and therefore they need to make certain theior import picks this up as alphanumeric
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jul 2013
    Posts
    3
    That worked thanks a million.

Tags for this Thread

Posting Permissions

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