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!
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.
'#Replace(getTXT.title, ",", ";", "ALL")#',
'NO',
'NO',
'YES',
<cfif getTXT.majcat eq 'Display'>'#Replace(getTXT
'#Replace(getTXT.mincat, "&", "and", "ALL")#',
'#getTXT.pnb#',
'#Replace(getTXT.shortdsc,
'#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
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.
'#Replace(getTXT.title, ",", ";", "ALL")#',
'NO',
'NO',
'YES',
<cfif getTXT.majcat eq 'Display'>'#Replace(getTXT
'#Replace(getTXT.mincat, "&", "and", "ALL")#',
'#getTXT.pnb#',
'#Replace(getTXT.shortdsc,
'#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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
use #REReplace(trim(getTXT.id)
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
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
> #REReplace(trim(getTXT.id)
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 ...
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 ...
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.
>> 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
well, that's what the op wants: "...I need to do a replace that replaces the letters with the number 1"
Azadi
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?p
make any sense? i expect not
Matt
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?p
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
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
> 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.
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.
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?
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?
> 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..
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..
Premium Content
You need an Expert Office subscription to comment.Start Free Trial