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

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]