Not selecting numbers in first 3 characters of cell only letters

  • File - left([File],3)

    output wanted in case statement...

    NJU000123 - NJU

    99392288 - NULL


    left([File],3 = office except if there are numbers.

    How can I improve this left statement to allow only for letters to be pulled while any cells with numbers in the first 3 get left out?

  • It's not 100% clear what you are trying to achieve.

    Maybe this will get you on the right path

    DECLARE @TestData table (StringVal varchar(10));

    INSERT INTO @TestData ( StringVal )
    VALUES ( 'NJU000123' ), ( '99392288' );

    SELECT d.StringVal
    , preFix = CASE WHEN PATINDEX('%[0-9]%', LEFT(d.StringVal, 3)) = 0 THEN LEFT(d.StringVal, 3) ELSE NULL END
    FROM @TestData AS d;
  • I agree that you aren't specific about what to do when the first three characters are something like N2X.     What if you have the string 'N2XY45689'?    Do you expect to get 'NX' (Letters in first three characters) or 'NXY' (First three letters).   In any case this will also give you a start.   Crude but effective.    Somebody could probably create a more elegant solution.

    declare @string varchar(20) = 'NBD334JU001234'

    ;With Fix1 (string) as ( select case when substring(@string,1,1) not like '[A-Z]'
    then stuff(LEFT(@string,3),1,1,' ')
    else LEFT(@string,3) end)
    ,Fix2 (string) as ( select case when substring(string,2,1) not like '[A-Z]'
    then stuff(string,2,1,' ')
    else string end
    from Fix1)

    ,Fix3 (string) as ( select case when len(string) = 3 and substring(string,3,1) not like '[A-Z]'
    then stuff(string,3,1,' ')
    else string end
    from Fix2)

    SELECT NULLIF(replace(STRING,' ',''),'')
    FROM Fix3


    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

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

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