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 SQl?

Highly appicate about it.

Jing

 

[jjonesal]

You can use a calucated field in your query (or code), but
try nesting the InStr function inside the Left function. The Instr function will return the column containing the first " ". Then, the Left function will return everything from characters 1 thru where you found the " ".

Of course if you have cities with spaces in the names, this won't work correctly.

newstring: Left([test],InStr(1,[test],' '))

Hope this helps...
jj

 

[MichaelRed]

You can use "Split" with a bit of trickery to get the results from the sample ("Chicago - providence - New York&quot

Just use the hyphen as the seperator cahracter, and add a record for each element of the array returned.

Split("Chicago - providence - New York", "-&quot

would return:
"Chicago "
" providence "
" New York"

You SHOULD trim the results (note the spaces in the sample return). The usage is:

MyVar = Split(str to spllit, seperator)

MyVar is a variant array (in this instance -of strings) and is zero based. So your process needs to include provisions for generating a variable number of records, based on the dimension 0f MyVar as returned from split.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over