Howto Format a string in a sproc ?

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.

Lookup STUFF in Books On Line

http://sqlservercode.blogspot.com/

Radu  (cuca_macaii2000@yah
oo.com)  writes:
> 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...

Denis suggested stuff(), but that will not take you far. T-SQL is
fairly poor on string manipulation, but with some creativity you can
do quite a bit. Here is one for you:

DECLARE @x varchar(20)
SELECT @x = 'Store #61'
SELECT @x =   right(replicate('0',
 5) +
right(@x, patindex('%[^0-9]%', reverse(@x)) - 1), 4)
SELECT @x

Parindex is use to locate the last non-digit in the string, and we
use reverse to look at the string backwards. right() picks the specified
characters at the end of the string.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

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@yaho
o.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.
>

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@yaho
o.com> wrote in message
> news:1127413452.303675.67220@g47g2000cwa.googlegroups.com... 
>
>

TSQL friggin rocks for string manipulation


i would reccomend replacing the # symbol and then inserting it
or better yet.. you should clean your data ONCE and dont do crap like
store 'STORE #61' in a text field-- can't you just store 61? as an int?

so you have clean data??