Post reply

Not Cast?

  • September 8, 2014 at 7:50 am

    #299155

    If a user enter a two digit code "20" my query will return the data for that code but I want to also return data for the code 0020 . Basically when its a two digit code I would like to add "00" to the code and return the data for both. When its a three digit code "786" I would like to return the code for 786 and 0786, (add one "0" to the code). What's the best or correct way to approach this?

  • September 8, 2014 at 8:24 am

    #1743879

    The operation varies depending on the data type you expect.

    DECLARE @String varchar(4) = '20',

    @String2 char(50) = '20',

    @int int = 20;

    SELECT RIGHT( '0000' + @String, 4), --Correct

    RIGHT( '0000' + @String2, 4), --Incorrect

    RIGHT( 10000 + @int, 4); --Correct

    GO

    Note the importance of using the correct data types.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • September 8, 2014 at 8:59 am

    #1743888

    DECLARE @Code VARCHAR(4)

    DROP TABLE #SampleData

    SELECT Code = CAST('20' AS VARCHAR(4)) INTO #SampleData UNION ALL

    SELECT '0020' UNION ALL

    SELECT '786' UNION ALL

    SELECT '0786'

    SET @Code = '20'

    SELECT *

    FROM #SampleData

    WHERE Code IN (CAST(CAST(@Code AS INT) AS VARCHAR(4)), RIGHT('0000'+@Code,4))

    SET @Code = '786'

    SELECT *

    FROM #SampleData

    WHERE Code IN (CAST(CAST(@Code AS INT) AS VARCHAR(4)), RIGHT('0000'+@Code,4))

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • September 8, 2014 at 2:07 pm

    #1743953

    Thank you.

Viewing 4 posts - 1 through 3 (of 3 total)

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