query for items beginning with a number

Posted on 2006-06-02
Last Modified: 2010-04-05
I have the following:

Name     ID                   REAL ID
Bob       12345              
Tom      AASDASD
Rick      aSASDASDA
Tim      12323r54

I need to populate the real ID column with the value in the ID column only when the value in the ID column starts with a number.  Any ideas???  The final results should look like:

Name     ID                   REAL ID
Bob       12345              12345    
Tom      AASDASD
Rick      aSASDASDA
Tim      12323r54          12323r54
Question by:ajslentz
    LVL 15

    Expert Comment

    Well if it starts with a number, then you only really need to check the very first character

    use the Val function

      i, code: Integer;
      // compare the first character of ID with an integer variable i
      Val(ID[1], i, code);
      // if the code returns 0 then the first character is a number
      if code = 0 then
        // do what you want with the ID here

    LVL 10

    Expert Comment

    You can also use SQL. e.g.

    update TableName set REAL ID=ID where left(ID, 1) in ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')

    Author Comment

    Is there a way to do this with a query, maybe in the where clause?  Otherwise, how would I copy this into the new cell as described in my example?

    LVL 10

    Expert Comment

    Of course you can do it with query. just copy the above code to the SQL property of the query and execute the query.

    e.g.  If u r using Access then use the following SQL. For other DBMS you can find an equivelant to IsNumeric function.
    Query1.SQL.Add('update TableName set REAL ID=ID where left(ID, 1) in ("0", "1", "2", "3", "4", "5", "6", "7", "8", "9"));

    If you want to update multiple records at a time the Query is better approach.
    LVL 10

    Accepted Solution

    If you are using Access then better SQL statement would be
    update TableName set REAL ID=ID where IsNumeric(left(ID, 1))

    Author Comment

    Thanks atul!!!  That was perfect!!!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Suggested Solutions

    Title # Comments Views Activity
    Synchonize thread with form 14 87
    Produce a sound in Delphi 11 93
    Save pdf file to other location 3 66
    IExtractImage Delphi 14 127
    Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
    In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now