Post reply

TSQL Challenge 46 - Remove leading occurrences of the first character

  • December 25, 2010 at 11:39 am

    #229095

    Comments posted to this topic are about the item TSQL Challenge 46 - Remove leading occurrences of the first character

    .

  • December 27, 2010 at 7:10 am

    #1266434

    UPDATE {table} SET columnN = SUBSTRING (columnN, 2, LEN(columnN));

  • December 27, 2010 at 9:22 am

    #1266474

    Wow! They actually made it feel like a business request. They actually left one of their "requirements" completely undefined and about as clear as lead. Requirement 1 is "Output should be sorted in ascending order based on the numeric value of the string". Some of the strings are "X8JXab", and "BBBBBBBBBBBBBBBA".

    Someone even asked, in their forum, what they mean by the "numeric" value of these strings. They haven't answered yet, though someone did post a guess as to the meaning (which is also like working with some devs, who don't clarify, they guess).

    It's also not clear if the sort should be on the "numeric value" of the input or the output string.

    Other than that requirement, this "challenge" is so trivial as to be silly. Are they supposed to be that way?

    (Ignoring the sort order, here's what I've got as a "what I came up with after about 10 seconds of thinking about it".)

    IF OBJECT_ID(N'tempdb..#TC46') IS NOT NULL

    DROP TABLE #TC46 ;

    CREATE TABLE #TC46 (String VARCHAR(MAX)) ;

    INSERT INTO

    #TC46 (String)

    VALUES

    ('aaaaaAbbcad'),

    ('X8JXab'),

    ('999744499XYZ'),

    ('BBBBBBBBBBBBBBBA'),

    ('AAAAAAAAAAAAAAAA') ;

    SELECT

    ISNULL(NULLIF(Stripped, String), SUBSTRING(String, 1, 1))

    FROM

    #TC46

    CROSS APPLY (SELECT

    SUBSTRING(String,

    PATINDEX('%[^' + SUBSTRING(String COLLATE SQL_Latin1_General_CP1_CS_AS,

    1, 1) + ']%', String),

    LEN(String) + 1) AS Stripped) AS LeadStripper ;

    I had to add to their test-set, since one of the requirements, that the input should be case-insensitive, but the output should be case-sensitive, isn't actually tested for in their sample strings.

    Edit: To clarify, I'm posting this here because I don't feel like posting an incomplete solution to the challenge, and can't post a complete one till they clarify the requirements. Since I probably won't bother checking back to see if they ever do, I figure I may as well post somewhere. 🙂

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

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

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