if field value numbers only then
-
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?
-
Yes it's possible but it's the application's job to do that. Why do you need to do this on the server?
-
I just didn't know if something like that would possible to do on Application side. I'll try that. SOrry for asking here =)
-
NP. Almost every string manipulation is easier at the client side.
-
What datatype is the field? Maybe you should simply set it to 'money'
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
It's already been said that it's possible, but this task should be done at the client.
-
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) -
Good point... I guess he'll have to tell me that for me to give that answer
. -
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)
Intro to Tally Tables and Functions -
"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
-
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) -
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)
Intro to Tally Tables and Functions -
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
. -
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 !!!** -
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)
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply