Solved

Removing spaces within column pre-output

Posted on 2003-12-07
6
138 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
Supports up to 4K resolution!

The VS192 2-Port 4K DisplayPort Splitter is perfect for anyone who needs to send one source of DisplayPort high definition video to two or four DisplayPort displays. The VS192 can split and also expand DisplayPort audio/video signal on two or four DisplayPort monitors.

 
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

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

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

One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
What You Need to Know when Searching for a Webhost Provider
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

622 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