bududa
asked on
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!
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!
jyokum
do you need the StateNoSpaces column for something in the database (like a join) or is it just for displaying?
ASKER
just for display
<cfquery name="qryData" datasource="whatever">
SELECT id, StateName
FROM tableName
</cfquery>
<cfoutput query="qryData">
#qryData.id# - #qryData.StateName# - #Replace(qryData.StateName
</cfoutput>
SELECT id, StateName
FROM tableName
</cfquery>
<cfoutput query="qryData">
#qryData.id# - #qryData.StateName# - #Replace(qryData.StateName
</cfoutput>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 :-)
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 :-)
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.
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.