By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,453 Members | 937 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,453 IT Pros & Developers. It's quick & easy.

Howto Format a string in a sproc ?

P: n/a
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.

Sep 22 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
SQL
Lookup STUFF in Books On Line

http://sqlservercode.blogspot.com/

Sep 22 '05 #2

P: n/a
Radu (cu*************@yahoo.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, es****@sommarskog.se

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

Sep 22 '05 #3

P: n/a
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" <cu*************@yahoo.com> wrote in message
news:11*********************@g47g2000cwa.googlegro ups.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.

Sep 27 '05 #4

P: n/a
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" <cu*************@yahoo.com> wrote in message
news:11*********************@g47g2000cwa.googlegro ups.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.


Sep 27 '05 #5

P: n/a
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??

Sep 28 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.