How can I replace letters leaving only numbers when inserting value into a table

halfbaked
halfbaked used Ask the Experts™
on
I have an upload page that inserts values from a text file into a table.  Within the thest file i have a field called 'ID' the values in this field contain integers and values with both numbers and letters. As the values are inserted into the table I need to do a replace that replaces the letters with the number 1.  example:

41ASTR234 becomes 411111234

My insert query looks like this:

 <cfloop query="getTXT">
            <cfquery name="products2" datasource="bbd">
              INSERT INTO products
            (
               ProductID,
               Status,
               man,
               model,
               PriceRunner,
               PriceGrabber,
               InStock,
               CategoryMain,
               Category,
               ManPartNo,
               BriefDesc,
               cost,
               rrp,
               saleprice,
               image,
               ingrams,
               image2,
               feedcategory                    
            )
            Values
            (
                '#getTXT.ID#',
                'Normal',
                <cfif getTXT.brand eq 'CISCO SYSTEMS'>'#Replace(getTXT.brand, "CISCO SYSTEMS", "Cisco", "ALL")#' <cfelse>'#Replace(getTXT.brand, "NETGEAR INC.", "Netgear", "ALL")#'</cfif>,
                '#Replace(getTXT.title, ",", ";", "ALL")#',
                'NO',
                'NO',
                'YES',
                <cfif getTXT.majcat eq 'Display'>'#Replace(getTXT.majcat, "Display", "Monitors", "ALL")#'<cfelseif getTXT.brand eq 'CISCO SYSTEMS'>'#Replace(getTXT.majcat, "Communications & Networking", "Cisco", "ALL")#'<cfelseif getTXT.majcat eq 'Cables'>'#Replace(getTXT.majcat, "Cables", "Cabling", "ALL")#'<cfelse>'#Replace(getTXT.majcat, "&", "and", "ALL")#'</cfif>,
                '#Replace(getTXT.mincat, "&", "and", "ALL")#',
                '#getTXT.pnb#',
                '#Replace(getTXT.shortdsc, ",", ";", "ALL")#',
                '#getTXT.costprice#',
                '0',
                '#getTXT.saleprice#',
                '#getTXT.imageurl#',
                'Y',
                'blank',
                'none'                
            )
            </cfquery>
    </cfloop>

so int his example i'm looking at the destination field being 'productID' that must contain only integers and we are looking at the '#getTXT.id#' which must include the replace function.

Any thoughts,

Thanks in advance
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
use #REReplace(trim(getTXT.id), "\D", "1", "ALL")#

the above uses regular expression pattern matching to replace all non-digit characters in the string with 1.
use the above instead of #getTXT.id# in your query.

Azadi
Most Valuable Expert 2015

Commented:
> #REReplace(trim(getTXT.id), "\D", "1", "ALL")#

    I think that would replace the non-numerics with other values, instead of just removing them. You probably
    mean:

      #REReplace( getTXT.id, "\D", "", "ALL")#

    You should also consider using CFQUERYPARAM ...

Most Valuable Expert 2015

Commented:
BTW:  You could end up with an empty string (ie non numeric) if the "ID" value contains only non-numbers like "ASTRAAAAA". If that's a problem, you should handle it in your code.  
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

>> I think that would replace the non-numerics with other values, instead of just removing them

well, that's what the op wants: "...I need to do a replace that replaces the letters with the number 1"


Azadi
Most Valuable Expert 2015

Commented:
> well, that's what the op wants:

   Well what do you know .. you're right.  Need ...more ...coffee.

Author

Commented:
Fortunately for me none of the values are only letters.  unfortunately i'm still having problems. The field in access that the ID is inserted into is my primary key and can only be an integer.  When I run my upload i'm getting a 'data type mis match error' suggesting that the replace isn't working.  I'm almost certain that this is not the best way to tackle my problem.  

The problem I have is that I have a text file that holds product details and and an ID number. When the info is inserted into the table the productID must always match the corresponding product.  by converting the letters to numbers i'm able to create a unique number for each product and a unique primary key which stays constant if new items are added to the text feed.  

any suggestions for how I can replace the letters or remove them but still keeping a unique but constant ID number for each product.  It is very importand that they remain constant for each product as my url produces  searchresults_detail.cfm?productid=473 and if this is used as a link it must always be the correct product.

make any sense?  i expect not

Matt
>> i'm getting a 'data type mis match error'

that's because you enclosed an integer value in single quotes - that tells the db it is text, not integer.

but on a more general subject, replacing non-digit characters with 1 can very easily result in a non-unique pk, as no doubt you realize.

you know that a pk column does not HAVE to be an integer? it can be a text/varchar datatype. this way you won't have to manipulate ids from text file in any way and, presuming they are already unique, will not run into duplicate pk problems.

Azadi
Most Valuable Expert 2015
Commented:
> I'm almost certain that this is not the best way to tackle my problem.  
   
    Personally the whole idea of depending on user input to establish a PK is tricky ... at best.  
    Not to mention, inserting data directly into a production table is dangerous.  You can easily hose
    your data.  I prefer inserting into a temp table, scrubbing the data, then only inserting into the prod. table if things
    are okay. Plus with the data sitting in a temp table, I can alert the user if there are problems.

Author

Commented:
I can't change my PK to text it throws out loads of my other code.  

what I thought originally was to change the each letter to a number so A=1 B=2 C=3 etc.  That would mean it would always be unique.  Can I do this in the INSERT query?  
Most Valuable Expert 2015

Commented:
> what I thought originally was to change the each letter to a number so A=1 B=2 C=3 etc

If you're just going to be making up an ID, why does it matter? Can't you just have your own PK (identity value, etc..) and associate it with this "other" value (41ASTR234).  But this begs the question .. what is this "other" value and how does it relate to your application? No offense, but this seems like a really bad way of dealing with PK's .. given how important they are and that they must be unique. You may want to give this some more thought..

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial