Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Removing spaces within column pre-output

Posted on 2003-12-07
6
Medium Priority
?
140 Views
Last Modified: 2013-12-24
I have the following list of topics in a column:

id, StateName
1, New York
2, Michigan
3, New Mexico

I need a way to create a new column via query without the spaces, so that I get:

id, StateName, StateNoSpaces
1, New York, NewYork
2, Michigan, Michigan
3, New Mexico, NewMexico

I tried to use a replace() function within the query but didn't get it right. I'm sure I had the syntax wrong however...

thanks!
0
Comment
Question by:bududa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
6 Comments
 
LVL 12

Expert Comment

by:jyokum
ID: 9894296
do you need the StateNoSpaces column for something in the database (like a join) or is it just for displaying?
0
 

Author Comment

by:bududa
ID: 9894403
just for display
0
 
LVL 12

Expert Comment

by:jyokum
ID: 9894411
<cfquery name="qryData" datasource="whatever">
SELECT id, StateName
FROM tableName
</cfquery>

<cfoutput query="qryData">
#qryData.id# - #qryData.StateName# - #Replace(qryData.StateName,' ','')#<br />
</cfoutput>
0
Connect further...control easier

With the ATEN CE624, you can now enjoy a high-quality visual experience powered by HDBaseT technology and the convenience of a single Cat6 cable to transmit uncompressed video with zero latency and multi-streaming for dual-view applications where remote access is required.

 
LVL 12

Accepted Solution

by:
jyokum earned 1500 total points
ID: 9894418
change the cfoutput block to this

<cfoutput query="qryData">
#qryData.id# - #qryData.StateName# - #Replace(qryData.StateName,' ','','ALL')#<br />
</cfoutput>

just in case StateName has multiple spaces
0
 
LVL 14

Expert Comment

by:Renante Entera
ID: 9894454
Actually you can simply output your data without calling again the QueryName.

You can simply have it this way :

<cfquery name="qryData" datasource="dsn">
SELECT id, StateName
FROM TableName
</cfquery>

<cfoutput query="qryData">
#id#, #StateName#, #Replace(StateName,' ','','ALL')#<br>
</cfoutput>

Goodluck!
eNTRANCE2002 :-)
0
 
LVL 12

Expert Comment

by:jyokum
ID: 9894502
entrance2002,
it's always best to qualify your variables. if you don't, CF has to do extra processing to figure out the scope of the variable. plus, it's a whole lot easier to read when you have to come back to this code months down the road and try to figure out what in the world you were doing.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

704 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