Solved

Removing spaces within column pre-output

Posted on 2003-12-07
6
132 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
  • 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
New! My Passport Wireless Pro Wi-Fi Mobile Storage

Portable wireless storage to offload, edit, and stream anywhere.

High-capacity, wireless mobile storage designed to accompany professional photographers and videographers in the field to easily offload, edit and stream captured photos and high-definition videos.

 
LVL 12

Accepted Solution

by:
jyokum earned 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.

914 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now