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)

  •  "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)

  • 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