String Manipulation problem

Hi there,

I'm using SQL Server 2005 and I have a coloumn look like this

tbl.Room.fldRoomNumber

1A
1A1
10A1
10A10
10A100
100C1
100B11

And so on, I want to split this to a prefix and suffix
Wich all numbers before letter will be in one coloumn
And all letters and numbers from the right and second coloumn

like this

Frefix Suffix
1 A
1 A1
10 A10
100 B11
100 C1
And so on

Thnaks,

Oded Dror

Here is one way:

SELECT keycol, x,
LEFT(x, PATINDEX('%[A-Z]%', x) - 1) AS prefix,
RIGHT(x, LEN(x) - PATINDEX('%[A-Z]%', x) + 1) AS suffix
FROM (SELECT 1, '1A'
UNION
SELECT 2, '1A1'
UNION
SELECT 3, '10A1'
UNION
SELECT 4, '10A10'
UNION
SELECT 5, '10A100'
UNION
SELECT 6, '100C1'
UNION
SELECT 7, '100B11') AS T(keycol, x);

--
Plamen Ratchev
http://www.SQLStudio.com

> And so on, I want to split this to a prefix and suffix
> Wich all numbers before letter will be in one coloumn
> And all letters and numbers from the right and second coloumn

Try something like:

SELECT
SUBSTRING(fldRoomNumber, 1, PATINDEX('%[^0-9]%',fldRoomNumber)-1) AS
Frefix,
SUBSTRING(fldRoomNumber, PATINDEX('%[^0-9]%',fldRoomNumber), 10) AS
Suffix
FROM dbo.Room


--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Oded Dror" wrote in message
news:%23vd2zDLCJHA.2476-at-TK2MSFTNGP06.phx.gbl...
> Hi there,
>
> I'm using SQL Server 2005 and I have a coloumn look like this
>
> tbl.Room.fldRoomNumber
>
> 1A
> 1A1
> 10A1
> 10A10
> 10A100
> 100C1
> 100B11
>
> And so on, I want to split this to a prefix and suffix
> Wich all numbers before letter will be in one coloumn
> And all letters and numbers from the right and second coloumn
>
> like this
>
> Frefix Suffix
> 1 A
> 1 A1
> 10 A10
> 100 B11
> 100 C1
> And so on
>
> Thnaks,
>
> Oded Dror
>

Dan,

It works thanks again

Oded

"Dan Guzman" wrote in message
news:O4YXkWLCJHA.4724-at-TK2MSFTNGP05.phx.gbl...
>> And so on, I want to split this to a prefix and suffix
>> Wich all numbers before letter will be in one coloumn
>> And all letters and numbers from the right and second coloumn
>
> Try something like:
>
> SELECT
> SUBSTRING(fldRoomNumber, 1, PATINDEX('%[^0-9]%',fldRoomNumber)-1) AS
> Frefix,
> SUBSTRING(fldRoomNumber, PATINDEX('%[^0-9]%',fldRoomNumber), 10) AS
> Suffix
> FROM dbo.Room
>
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
>
> "Oded Dror" wrote in message
> news:%23vd2zDLCJHA.2476-at-TK2MSFTNGP06.phx.gbl...
>> Hi there,
>>
>> I'm using SQL Server 2005 and I have a coloumn look like this
>>
>> tbl.Room.fldRoomNumber
>>
>> 1A
>> 1A1
>> 10A1
>> 10A10
>> 10A100
>> 100C1
>> 100B11
>>
>> And so on, I want to split this to a prefix and suffix
>> Wich all numbers before letter will be in one coloumn
>> And all letters and numbers from the right and second coloumn
>>
>> like this
>>
>> Frefix Suffix
>> 1 A
>> 1 A1
>> 10 A10
>> 100 B11
>> 100 C1
>> And so on
>>
>> Thnaks,
>>
>> Oded Dror
>>
>