Slpitting a string

21 Oct 2005 7:02 PM

Asher_N

I have a badly designed legacy database. The product number column has the
format 'xxx -yyy', where x is the style and y is the colour. Both style
and colour have a variable length. They are always separated by 3 blamks
and a '-'. I need to be able to isolate the colour part in order to join to
a colour description table. I can't erdesign the table and I can't use a
stored procedure.

Is it possible to do in T-SQL?

21 Oct 2005 7:14 PM

Aaron Bertrand [SQL Server MVP]

You didn't provide enough information for a well-formed or test-able query,
but something like this might help:

SELECT a.ColorDescription, b.ProductNumber
    FROM a
    INNER JOIN b
    ON a.ColorDescription = RTRIM(LEFT(b.ProductNumber, CHARINDEX('-',
b.ProductNumber)))

Show quote

"Asher_N" <compguy***@hotmail.com> wrote in message
news:Xns96F698F3EA0B1compguy666hotmailcom@207.46.248.16...
>I have a badly designed legacy database. The product number column has the
> format 'xxx -yyy', where x is the style and y is the colour. Both style
> and colour have a variable length. They are always separated by 3 blamks
> and a '-'. I need to be able to isolate the colour part in order to join
> to
> a colour description table. I can't erdesign the table and I can't use a
> stored procedure.
>
> Is it possible to do in T-SQL?

21 Oct 2005 7:22 PM

Prabhu Sadasivam (PS)

Yes, You can. Try this:

SELECT myProductColumn,
RTRIM(LEFT(col1, CHARINDEX ('-', myProductColumn, 1)-4)) AS [ProductNumber]
FROM MyTable

-Prabhu

Show quote

"Asher_N" wrote:

> I have a badly designed legacy database. The product number column has the
> format 'xxx -yyy', where x is the style and y is the colour. Both style
> and colour have a variable length. They are always separated by 3 blamks
> and a '-'. I need to be able to isolate the colour part in order to join to
> a colour description table. I can't erdesign the table and I can't use a
> stored procedure.
>
> Is it possible to do in T-SQL?
>

21 Oct 2005 8:14 PM

Asher_N

That works. Thanks.

Damn you guys are good.

=?Utf-8?B?UHJhYmh1IFNhZGFzaXZhbSAoUFMp?= <Prabhu Sadasivam
(P**@discussions.microsoft.com> wrote in
Show quote

news:DEAA5DFC-A520-425F-B1AC-BB3CCF297BA4@microsoft.com:

> Yes, You can. Try this:
>
> SELECT myProductColumn,
> RTRIM(LEFT(col1, CHARINDEX ('-', myProductColumn, 1)-4)) AS
> [ProductNumber] FROM MyTable
>
> -Prabhu
>
> "Asher_N" wrote:
>
>> I have a badly designed legacy database. The product number column
>> has the format 'xxx -yyy', where x is the style and y is the
>> colour. Both style and colour have a variable length. They are always
>> separated by 3 blamks and a '-'. I need to be able to isolate the
>> colour part in order to join to a colour description table. I can't
>> erdesign the table and I can't use a stored procedure.
>>
>> Is it possible to do in T-SQL?
>>
>