Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 142
  • Last Modified:

Removing spaces within column pre-output

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
bududa
Asked:
bududa
  • 4
1 Solution
 
jyokumCommented:
do you need the StateNoSpaces column for something in the database (like a join) or is it just for displaying?
0
 
bududaAuthor Commented:
just for display
0
 
jyokumCommented:
<cfquery name="qryData" datasource="whatever">
SELECT id, StateName
FROM tableName
</cfquery>

<cfoutput query="qryData">
#qryData.id# - #qryData.StateName# - #Replace(qryData.StateName,' ','')#<br />
</cfoutput>
0
Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

 
jyokumCommented:
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
 
Renante EnteraCommented:
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
 
jyokumCommented:
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now