split string

[site]

Hi, All,

I have the table like that:

MC# TYPE TERRITORY
----------------------------------------------
005 Taxi Chicago - providence - New York
...

I want to get result like that:

MC# TYPE TERRITORY
----------------------------------------------
005 Taxi Chicago
005 Taxi Providence
005 Taxi New York
...

Is is possible to write stored procedure? Or other ways?

Highly appicate about it.

Jing

 

[tlbroadbent]

Jing,

You've posted this in the Access and SQL Server forums. The solution will differ depending on the RDMS. Which database are you using?

In the future, choose the correct forum and post there only. May I suggest that you join TEK-TIPS and take advantage of the full power of the site rather than just posting questions as a visitor. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[site]

Hi, Terry,

I am doing a MS Access project and connection to SQL server. Actually, I am a real newer to face this project.
I just need this new table, either way to using SQL or stored procedure or other way.

By the way, I will join your group today.

Thanks again.
Jing

 

[tlbroadbent]

Jing,

Are the city names always separated by " - " (space dash space) if more than one city is listed in the Territory column? You indicate that you want to create a new table? Did I understand that correctly? Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[site]

Hi, Terry,

Yes, the City always separated by " - "(at most four cities). In my table I have three fields, I want to split out "TERRITORY" field separated by city and shared with first two fields info. That means look like the new table.
****************
Old table:

MC# TYPE TERRITORY
----------------------------------------------
005 Taxi Chicago - providence - New York
009 Cab Westerly - NewPort - Middletown - Boston
...

New table:

MC# TYPE TERRITORY
----------------------------------------------
005 Taxi Chicago
005 Taxi Providence
005 Taxi New York
009 Cab Westerly
009 Cab NewPort
009 Cab Middletown
009 Cab Boston
...

Thank you a lots.
Jing

 

[rajeevnandanmishra]

Hi jing,
Try this,

create procedure myProc as
select mc, type, territory, 'n' as flg
into #myTable from myTable
select mc, type, territory
into #myTableNew from myTable where 1=2
declare @totrow int, @instr int
declare @newstr1 varchar(100), @newstr2 varchar(100)
declare @mc char(3), @type char(4),
select @totrow=count(*), @instr=0 from #myTable
while @totrow>0
begin
set rowcount 1
select @newstr1='~', @newstr2='~', @instr=0
select @mc=mc, @type=type,
@newstr1=territory from #myTable where flg='n'
select @instr=charindex(' - ',@newstr1)
if @instr=0
insert #myTableNew
values(@mc,@type,@newstr1)
while @instr>0
begin
select @newstr2=substring(@newstr1,1,@instr),
@newstr1=substring(@newstr1,@instr+3, DATALENGTH(@newstr1)-@instr)
select @instr=charindex(' - ',@newstr1)
insert #myTableNew
values(@mc,@type,@newstr2)
if @instr=0
insert #myTableNew
values(@mc,@type,@newstr1)
end
update #myTable
set flg='y' where mc=@mc
select @totrow=@totrow-1
end
set rowcount 0
select * from #myTableNew
drop table #myTableNew
drop table #myTable

This procedure return you the required result set.