Split data in a field 2

[Hunguyen]

I have a field where I would like to split the string into two fields. The string contains no spaces. The first (L-R)six characters is the postal code and the seventh character is a code#. I would like to split the postal code from the code#.

Ex.

FIELD1
V0N1Y67
V0N1Y69
V0N2X46
V0N1Y62

Desired result:

FIELD1 FIELD2
V0N1Y6 7
V0N1Y6 9
V0N2X4 6
V0N1Y6 2

This seems like it would be a common problem, but I have not been able to find a solution for it.

 

[r937]

SELECT LEFT(FIELD1,6) AS FIELD1
, RIGHT(FIELD1,1) AS FIELD2
FROM ...

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[HarleyQuinn]

Something like:

Select Left(Field1,6) as NewField1, Right(Field1,1) as Field2
From YourTable

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.