473,320 Members | 2,094 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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.

Sep 22 '05 #1
5 12993
SQL
Lookup STUFF in Books On Line

http://sqlservercode.blogspot.com/

Sep 22 '05 #2
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Krish | last post by:
Hi, How to convert "1283912839E-5" string (in scientific format) to a decimal. Convert.ToDecimal("1283912839E-5") is throwing "Input string is not in correct format" exception. Is the...
1
by: Radu | last post by:
Hi. Thru a sproc, I drop & re-create some temp tables. When I call that sproc from the client, though, I cannot drop the tables. I need to allow the user, say "Alex", to drop/create tables...
3
by: Radu | last post by:
Hi. I have lots of processing to do on the server - from the client (Access) I call a sproc which returns a recordset (the sproc is essentially a big "select"). With the obtained data , I need to...
18
by: Steve Litvack | last post by:
Hello, I have built an XMLDocument object instance and I get the following string when I examine the InnerXml property: <?xml version=\"1.0\"?><ROOT><UserData UserID=\"2282\"><Tag1...
4
by: Chad Micheal Lawson via .NET 247 | last post by:
I'm stumped at this point and I'm tired of trying things so I'mposting in hopes of some guru with a sharp eye. I have anasp.net app running on a local Win XP Pro box. Within the app,I call a SPROC...
5
by: Tim::.. | last post by:
Can someone please tell me if it is possible to get the SQL statement from a stored procedure in ASP.NET? Dim Myconn As New SqlConnection(ConfigurationSettings.AppSettings("strConn")) Dim cmd As...
4
by: yer darn tootin | last post by:
Does anyone know the sort expression for a column that's data has been returned in the format, eg '07 Jul 05'?? The sort expression {..:"dd mmm yy"} doesn't work ( if the column was returned as...
1
by: Roy | last post by:
Hi, I have a problem that I have been working with for a while. I need to be able from server side (asp.net) to detect that the file i'm streaming down to the client is saved...
2
by: Jose Oliver | last post by:
I am a bit baffled on this error. A windows app written in C# is attempting to execute the following query: SELECT * FROM Employee WHERE (Name LIKE '%' + @PARAM1 + '%') AND...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.