asked on

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

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

azadisaryev

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

_agx_

> #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 ...

_agx_

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.

azadisaryev

>> 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

_agx_

> well, that's what the op wants:

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

halfbaked

ASKER

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

azadisaryev

>> 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

ASKER CERTIFIED SOLUTION

_agx_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

halfbaked

ASKER

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?

_agx_

> 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..