query for items beginning with a number

Posted on 2006-06-02
Medium Priority
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
  • 3
  • 2
LVL 15

Expert Comment

ID: 16817445
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

ID: 16817512
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

ID: 16817521
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?

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

LVL 10

Expert Comment

ID: 16817595
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

atul_parmar earned 2000 total points
ID: 16817824
If you are using Access then better SQL statement would be
update TableName set REAL ID=ID where IsNumeric(left(ID, 1))

Author Comment

ID: 16818720
Thanks atul!!!  That was perfect!!!

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses
Course of the Month15 days, 2 hours left to enroll

840 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