Devide 1 feild into 3 feilds!

Hi there!

There is a feild in my table that contains the city, state and zip, all in the same feild. I was wondering how would be the best way to devide all that info up into 3 feilds? i really don't know how I would go about it since there are cities that can be comosed of 2 or more words. Here is some sample data. I hope someone can help.

SOUTH EL MONTE CA91733617
BOSSIER LA71172
GARDENA CA90249107
MILWAUKEE WI53216
PARIS IL61944
DUQUOIN IL62832
REDWOOD FALLS MN56283
AUBURN ME04210

IRWINDALE CA91706048
PORTLAND OR97202901
PORTLAND OR972028901
ANAHEIM CA928071735
KENT WA98032
CRYSTAL LAKE IL60014611Why don't u try to extract from the right end..i.e zip first ..then State and city...since zip and state are single words it should work...|||I have tried to extract ur reuirement...I think this would meet ur requirement...

create table test(field varchar(100))
go
insert into test
select 'REDWOOD FALLS MN56283'
select 'SOUTH EL MONTE CA91733617' Union all
select 'BOSSIER LA71172' Union all
select 'GARDENA CA90249107'

go

select reverse(substring(reverse(field),charindex(' ',reverse(field))+1,len(field))) 'City',
substring(reverse(substring(reverse(field),1,CHARI NDEX ( ' ' , reverse(field) )-1)),1,2) 'State',
substring(reverse(substring(reverse(field),1,CHARI NDEX ( ' ' , reverse(field) )-1)),3,len(reverse(substring(reverse(field),1,CHARI NDEX ( ' ' , reverse(field) )-1)))) 'Zip'
from test

This code may not me the best performing query...I just wanted the query work...

Developer Access

Can someone advise potential security concerns in giving VIEW SERVER STATE
access to developers on production server?guest5.
VIEW SERVER STATE exposes information about the server and its current
state. (As you might guess by the name.) This allows you to see all
processes that are running in 'sp_who' and, in general, makes the statistics
in the dynamic management views visible. It also allows a user to use DBCC
INPUTBUFFER
In the Books Online if you search for "VIEW SERVER STATE" you will get about
110 things reported there, if you want to review the details.
The question is: Who needs this level of information? I think it is
reasonable for those who actively support the databases and server to have
this access, but I would not make it generally available.
RLF
"guest5" <guest5@.discussions.microsoft.com> wrote in message
news:20CC60D6-8D82-4CBB-B6C8-9D2466B27624@.microsoft.com...
> Can someone advise potential security concerns in giving VIEW SERVER STATE
> access to developers on production server?

Determining trigger execution state

Is there a way to determine if a trigger fired due to an insert or update
operation. I need to know this for the following reason. I have fields in
my tables that I want to initialize only when a record is inserted. If the
record is later updated, I do not want these fields changed. I also do not
want to have to maintain two seperate triggers to do this. In SqlAnywhere,
they have variables you check in the trigger to determine this as the
following example illustrates.
IF Inserting
!do inserting code
END IF
IF Updating
!do updating code
END IF
Is there something similar to this in SQL server?
Thanks in advance for any help.
Mark GenovyHi Mark
If any rows were affected, an insert trigger will only populate the inserted
table, but an update trigger will populate both inserted and deleted. So I
usually just look at the count(*) value from these tables. However, if no
rows were affected, the triggers will still fire, and both inserted and
deleted will have 0 rows. So then you can't tell what operation occurred.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Mark Genovy" <Mark Genovy@.discussions.microsoft.com> wrote in message
news:24381F86-0662-4913-A703-139BC0A9DF46@.microsoft.com...
> Is there a way to determine if a trigger fired due to an insert or update
> operation. I need to know this for the following reason. I have fields
> in
> my tables that I want to initialize only when a record is inserted. If
> the
> record is later updated, I do not want these fields changed. I also do
> not
> want to have to maintain two seperate triggers to do this. In
> SqlAnywhere,
> they have variables you check in the trigger to determine this as the
> following example illustrates.
> IF Inserting
> !do inserting code
> END IF
> IF Updating
> !do updating code
> END IF
> Is there something similar to this in SQL server?
> Thanks in advance for any help.
> Mark Genovy