Replace 1st char in a string if not null

  • Jay@Work

    SSCrazy

    Points: 2417

    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

  • Thom A

    SSC Guru

    Points: 98023

    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.

  • Jay@Work

    SSCrazy

    Points: 2417

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

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

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