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

  • This was removed by the editor as SPAM

  • MaryWard, this is a SQL Server forum. Moving data to Excel and processing it there is a really bad idea!

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See for details of how to post T-SQL code-related questions.

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

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