Post reply

Replace 1st char in a string if not null

  • April 2, 2019 at 7:23 pm

    #2538733

    I'm doing an active directory extract using t-sql and need to manipulate the mobile column to prefix it with the country code and drop the first character. Prefixing the country code is simple using '999' + mobile

    But I also need to drop the first char as this is not required when calling from out of the country.

    So if the underlying data is '0123123123' I need the result to be '999123123123' I need this to handle the possibility of the column being NULL, probably best to trim any leading spaces while we're at it. This select is wrapped around an OpenQuery so the string manipulation will need to happen as part of the outer select....

    SELECT top 901, '999' + mobile as 'NZ mobile' FROM OpenQuery (

    ADSI,

    'SELECT mail, mobile, telephoneNumber, title, sn, givenName, SAMAccountName FROM ''LDAP://xxxxxxx'' WHERE objectClass = ''user'' AND givenName<=''z'' AND title>=''a''') AS tblADS

  • April 2, 2019 at 7:26 pm

    #2539178

    STUFF is really handy for things like this:

    STUFF(Mobile,1,1,'')

    That will remove the first character of the from the column Mobile. The function is very commonly used when creating delimited list in SQL Server, as removing the first character from a string is far easier than removing the last.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • April 2, 2019 at 8:44 pm

    #2546159

    Works perfectly, thanks! Also doesn't complain when it is passed NULL which is a bonus

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

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