Solved

Removing spaces within column pre-output

Posted on 2003-12-07
6
136 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Manage your data center from practically anywhere

The KN8164V features HD resolution of 1920 x 1200, FIPS 140-2 with level 1 security standards and virtual media transmissions at twice the speed. Built for reliability, the KN series provides local console and remote over IP access, ensuring 24/7 availability to all servers.

Question has a verified solution.

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

Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

756 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