Separating Data

[tsyle]

I have a column in my database like the one below.

ID1
1092200001
1092200002
1234567890
2678100001
2678100002
2678100003
2678100004
2678100005
5112300001
5329500001
5329500002
5329500003
5329500004
5329500005

What i am trying to do is create a query where it will separate the first 4 number, next 2 numbers, and then all the rest of the numbers. For example, using these data..

5329500003
5329500004
5329500005

I want to create 3 columns. Col1 will have 5329, Col2 will have 50, and Col3 will have 0003 and 0004

So basically i just want to separate the column into 3 different columns. Is this possible to do?

 

[Remou]

Hi
How about:

SELECT Mid([MyColStr],1,2) AS [Col 1], Mid([MyColStr],3,2) AS [Col 2], Mid([MyColStr],1,2) AS [Col 3]
FROM MyTable;

 

[PHV]

SELECT Left([ID1],4) AS Col1, Mid([ID1],5,2) AS Col2, Mid([ID1],7) AS Col3
FROM yourTable

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886