"Mr. Smith" <nospam@nospam.com> wrote in message
news:e3nHQ7sTHHA.4028@TK2MSFTNGP02.phx.gbl...
> Hi.
> I have product id's on the form:
> ABC000001
> ABC000002
> ABCD000001
> ABCD000002
>
> How can I extract the left "character" part of the string (ABC and ABCD)
> since it does not work with left(string,3).
>
> I want this kind of code:
> left(string,until find(0-9))
>
> Any hints appreciated
>
> Kind regards
> Mr. Smith
>

"Mr. Smith" <nospam@nospam.com> wrote in message
news:e3nHQ7sTHHA.4028@TK2MSFTNGP02.phx.gbl...
> Hi.
> I have product id's on the form:
> ABC000001
> ABC000002
> ABCD000001
> ABCD000002
>
> How can I extract the left "character" part of the string (ABC and ABCD)
> since it does not work with left(string,3).
>
> I want this kind of code:
> left(string,until find(0-9))
>
> Any hints appreciated
>
> Kind regards
> Mr. Smith
>

> I'm trying to use your code in a SET statement to put the result from your
> algorithm into a dedicated field in the same table.
>
> As in:
> UPDATE tblproducts SET field_charcode = 'Your code'

"Mr. Smith" <nospam@nospam.com> wrote in message news:e3nHQ7sTHHA.4028@TK2

MSFTNGP02.phx.gbl...
> Hi.
> I have product id's on the form:
> ABC000001
> ABC000002
> ABCD000001
> ABCD000002
>
> How can I extract the left "character" part of the string (ABC and ABCD)
> since it does not work with left(string,3).
>
> I want this kind of code:
> left(string,until find(0-9))
>
> Any hints appreciated
>
> Kind regards
> Mr. Smith
>

"Mr. Smith" <nospam@nospam.com> wrote in message
news:O%23HlTtvTHHA.3316@TK2MSFTNGP02.phx.gbl...
> Ok, A.
> What I'm doing is to raise a relational framework based on a "flat"
> datastructure.
>
> The character part of the productid, will be the foreign key to make a
> relationship to the supplier table, where each supplier is uniqly
> identified by a character code.
>
> Product ABC00001 is a product from supplier with the uniqe identifier ABC.
>
> I share your view on putting the logic to work at query time, and I'll
> guess I'll manage to build a "virtual" relationship through a view, rather
> than a permanent table to table relationship....
>
> Please share your thoughts if you have other suggestion given my scenario
> description.
>
> Thanks again.
> Mr. Smith
>
>
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in
> message news:%23xRz1fvTHHA.3500@TK2MSFTNGP05.phx.gbl...
>>> I'm trying to use your code in a SET statement to put the result from
>>> your algorithm into a dedicated field in the same table.
>>>
>>> As in:
>>> UPDATE tblproducts SET field_charcode = 'Your code'
>>
>> Are you going to update the entire table every time any INSERT/UPDATE
>> statement occurs?
>>
>> I suggest NOT storing this information, because it can always be derived
>> at query time. If you need to, create a view that uses my logic and add
>> the column there, instead of persisting it.
>>
>> A
>>
>
>

"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:OJNpt$sTHHA.3948@TK2MSFTNGP05.phx.gbl...
> CREATE TABLE #foo
> (
> s VARCHAR(32)
> );
>
> SET NOCOUNT ON;
>
> INSERT #foo SELECT 'ABC000001'
> INSERT #foo SELECT 'ABC000002'
> INSERT #foo SELECT 'ABCD000001'
> INSERT #foo SELECT 'ABCD000002'
> INSERT #foo SELECT 'XYZ5'
> INSERT #foo SELECT 'XYZ' -- no numbers
> INSERT #foo SELECT '555' -- no letters
>
>
> SELECT
> s,
> CASE WHEN f > 0 THEN LEFT (s,f-1)
> ELSE s
> END
> FROM
> (
> SELECT
> s,
> f = PATINDEX('%[0-9]%', s)
> FROM
> #foo
> ) x;
>
>
>
>
>
>
> DROP TABLE #foo;
> "Mr. Smith" <nospam@nospam.com> wrote in message
> news:e3nHQ7sTHHA.4028@TK2MSFTNGP02.phx.gbl...
>> Hi.
>> I have product id's on the form:
>> ABC000001
>> ABC000002
>> ABCD000001
>> ABCD000002
>>
>> How can I extract the left "character" part of the string (ABC and ABCD)
>> since it does not work with left(string,3).
>>
>> I want this kind of code:
>> left(string,until find(0-9))
>>
>> Any hints appreciated
>>
>> Kind regards
>> Mr. Smith
>>
>
>

"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:%23xRz1fvTHHA.3500@TK2MSFTNGP05.phx.gbl...
>> I'm trying to use your code in a SET statement to put the result from
>> your algorithm into a dedicated field in the same table.
>>
>> As in:
>> UPDATE tblproducts SET field_charcode = 'Your code'
>
> Are you going to update the entire table every time any INSERT/UPDATE
> statement occurs?
>
> I suggest NOT storing this information, because it can always be derived
> at query time. If you need to, create a view that uses my logic and add
> the column there, instead of persisting it.
>
> A
>