Microsoft SQL Server forum - Howto Format a string in a sproc ?

Author

Howto Format a string in a sproc ?

Radu

2005-09-22, 8:24 pm

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.

SQL

2005-09-22, 8:24 pm

Lookup STUFF in Books On Line

http://sqlservercode.blogspot.com/

Erland Sommarskog

2005-09-22, 8:24 pm

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

GeoSynch

2005-09-27, 3:23 am

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.
>

GeoSynch

2005-09-27, 3:23 am

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...
>
>

dbahooker@hotmail.com

2005-09-28, 11:23 am

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??

Sponsored Links