Posted by GeoSynch on 09/27/05 08:57

Deleted the extraneous 2nd SELECT
and hopefully the formatting is a little
more legible.

SELECT REPLICATE('0', 4 - LEN(
REPLACE(
SUBSTR([FieldName],
PATINDEX( '%Store ', [FieldName]),
LEN([FieldName]) - PATINDEX( '%Store ',
[FieldName])
), '#', ''))) +
REPLACE(
SUBSTR([FieldName],
PATINDEX( '%Store ', [FieldName]),
LEN([FieldName]) - PATINDEX( '%Store ',
[FieldName])
), '#', '') AS StoreNum



GeoSynch

> Give this a try - it should do what you're trying to do.
> Replace [FieldName] with that in your database.
> 'Store ' is what we're looking for and we'll replace
> the '#' char with nothing if we find it and we'll add
> leading zeros as necessary to make length equal 4 chars.
>
> SELECT REPLICATE('0', 4 - LEN(
> REPLACE(
> SUBSTR([FieldName],
> PATINDEX( '%Store ', [FieldName]),
> LEN([FieldName]) - PATINDEX( '%Store ',
> [FieldName])
> ), '
> #', ''))) +
> SELECT REPLACE(
> SUBSTR([FieldName],
> PATINDEX( '%Store ', [FieldName]),
> LEN([FieldName]) - PATINDEX( '%Store ',
> [FieldName])
> ), '
> #', '') AS StoreNum
>
>
> GeoSynch
>
>
> "Radu" <cuca_macaii2000@yahoo.com> wrote in message
> news:1127413452.303675.67220@g47g2000cwa.googlegroups.com...
>> Hi. I have data which comes as a string like
>>
>> "... Store #61"
>> but sometimes it is
>>
>> "... Store 61"
>> and sometimes it is
>>
>> "... Store 061"
>>
>> For three digits, it might be "... Store 561" or "... Store #561", or
>> "... Store 0561".....
>>
>> The only thing I can be sure of is that the last 2 or 3 (significant)
>> digits of this field represent the StoreNumber.
>>
>> I have to link this table on field StoreNumber with another table where
>> the data is ALWAYS like 0061, 0561, etc, so always four digits, padded
>> with zeroes.
>>
>> I'd like to use the equivalent of the VB function
>> Format(StoreNumber), "0000"), but Format does NOT exist in TSQL.
>>
>> How could I solve this problem ? Please bear with me - I'm a beginner
>> in SQL...
>>
>> Thank you very much
>>
>> Alex.
>>
>
>

[Back to original message]