April 2, 2008 at 10:15 am
Hi I have a table with a field of geographic areas that relate to and area on a map. All the area's end in 'O' to identify the province that they are in 'Ontario'. What I would like to do is return the column without the 'O' at the end of the area.
Here is a list of example areas in the area column
create table #tb_area
(
area varchar(50)
)
insert into #tb_area
(area)
select 'DOWNTOWNO' union all
select 'TORADELAIDEO' union all
select 'TORDONLANDSO' union all
select 'WATUNIVERSITYO' union all
select 'GUELPHWILLOWO' union all
select 'OTTAWAHULLCOREO' union all
select 'BURLINGTONGUELPHO'
from this data set how would I return each record with the area's excluding the last 'O' from each area?
Thank you in advance for your help.
April 2, 2008 at 10:22 am
Check out the SUBSTRING function.
It takes three parameters:
1. The expression to search
2. The start place of what to return
3. The length of characters to return. In this instance, subtract 1 from the length of the string. The length can be found with the LEN function.
SUBSTRING({place}, 1, LEN([place}) - 1)
Dave Novak
April 2, 2008 at 10:29 am
Since you need all the of the string other than the last character, you can use LEFT.
Here is how you can use it
create table #tb_area
(
area varchar(50)
)
insert into #tb_area
(area)
select 'DOWNTOWNO' union all
select 'TORADELAIDEO' union all
select 'TORDONLANDSO' union all
select 'WATUNIVERSITYO' union all
select 'GUELPHWILLOWO' union all
select 'OTTAWAHULLCOREO' union all
select 'BURLINGTONGUELPHO'
select left(area,len(area)-1)
from #tb_area
drop table #tb_area
April 2, 2008 at 10:34 am
and where right(string,1) = 'O'
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 2, 2008 at 10:39 am
Thanks, gents, both the substing and left functions with the where condition worked for me.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply