if field value numbers only then

Question

Post reply

if field value numbers only then

hren

Ten Centuries

Points: 1220
More actions
September 2, 2005 at 1:59 pm

#88951

When I select field from a table how can I do replacement/append
FOr example if value of the field has numbers only, then append '$' sign in front of it and '.00' at the end. Is this possible?

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Yes it's possible but it's the application's job to do that. Why do you need to do this on the server?

hren Ten Centuries Points: 1220 More actions · Answer 2

I just didn't know if something like that would possible to do on Application side. I'll try that. SOrry for asking here =)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

NP. Almost every string manipulation is easier at the client side.

chisholmd SSCrazy Points: 2490 More actions · Answer 4

What datatype is the field? Maybe you should simply set it to 'money'

Dave

Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

It's already been said that it's possible, but this task should be done at the client.

chisholmd SSCrazy Points: 2490 More actions · Answer 6

granted but if there are many clients (types) and the formating wants to be enforced enterprise wide...

Dave

Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Good point... I guess he'll have to tell me that for me to give that answer .

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

HRen,

You could certainly change the meat of this to a user defined function to, as they say, enforce an enterprise wide standard or if the output is required for something other than a "presentation layer app"

/*******************************************************************

Demonstration of how to conditionally format a column to a format

of $nnn,nnn,nnn.nn if the value is numeric only (decimal point

allowed) and returns the original value if not 100% numeric. Most

of the code here is just a setup for the demo. The meat of the

demo is in the CASE statement.

--Jeff Moden

*******************************************************************/

--===== If experimental table exists, drop it

IF OBJECT_ID('TempDB..#yourtable') IS NOT NULL

DROP TABLE #yourtable

--===== Create the experimental table

CREATE TABLE #yourtable

(ID INT IDENTITY(1,1),

YourField VARCHAR(20))

--===== Populate the experimental table with values

INSERT INTO #yourtable (YourField)

SELECT 'A123.45' UNION ALL

SELECT '123A.45' UNION ALL

SELECT '123.A45' UNION ALL

SELECT '123.45A' UNION ALL

SELECT '123.45' UNION ALL

SELECT '0000123.45' UNION ALL

SELECT '123.450000' UNION ALL

SELECT '12345'

--===== If value of the field has numbers only,

-- then append '$' sign in front of it and '.00' at the end.

SELECT YourField AS OrigVal,

CASE

WHEN YourField NOT LIKE '%[^0-9,.]%'

THEN '$'+CONVERT(VARCHAR(24),CONVERT(MONEY,YourField),1)

ELSE YourField

END AS DispVal

FROM #yourtable

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Michael Du Bois SSCertifiable Points: 5163 More actions · Answer 9

"but if there are many clients (types) and the formating wants to be enforced enterprise wide..."

What if the application does not need/want the formating? If the business wants to enforce the rule enterprise wide it should insist that the application programers convert the data on the client side to meet their standards.

If the application was developed by a third party and does not convert the data then the logic of the application may crash and burn if you force the formating before passing the data.

As a DBA can never know how client will use the data today much less 6 months from now leaving the formating of data to the client makes everyone's life easer. And of course formating data on the server can have a serious negative impact on performance.

Mike

chisholmd SSCrazy Points: 2490 More actions · Answer 10

What a funny thread.

Sure there are plenty of ways to deal with it and all depend on what the needs and environemnt are.

If somebody asks a formating question one can provide an answer and then perhaps a better strategy.

This wasn't my question, and I am personally always happy for any/all the help I get when I need it. However when I am asking how to do "A" and somebody suggests I use a different alphabet it's not always helpful.

imho

Dave

Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 11

Concur with Chisholmd... it's a funny thread the manner of which seems to occur all too often and the original poster is driven away without an answer to the original question.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 12

I might have to change my way of thinking on those questions... but still when someone asks how to do something in dynamic sql, I'll ALWAYS try to make them change back to static .

sushila SSC-Dedicated Points: 35293 More actions · Answer 13

chiming in with my 2Cs here...agree with Chisholmd as well - whenever I answer a post I always try to respond specifically to the question (unless I don't understand it at all or there are murky clouds)...

however, with that being said - oftentimes it is difficult to really know if the poster is a rookie who asks something out of ignorance or if he/she is actually a wiz at what they do and asks because they've weighed all available options carefully...

Chisholmd - if you think this thread is funny, you'll probably die laughing if I manage to find the links where the original poster has said "thank you much for all the help" and the people who have jumped in to help are still arguing long and hard about the best solution - one of these days I am going to compile a list of all such links...

**ASCII stupid question, get a stupid ANSI !!!**

chisholmd SSCrazy Points: 2490 More actions · Answer 14

I think its what makes sqlservercentral.com such a great forum. Everybody is so willing to debate all aspects of a problem.

I have rarely had to wait more the 15min for a response to my questions and the responses are very comprehensive.

Dave

Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors)