How to get string starting after particular character in SQL Server

I have table ABC which has a column xyx that contains values like (888) 477-8977x110 or (888) 477-8977 ext 110.

How do I get values after x or ext in SQL Server

4 answers

  • answered 2018-01-11 19:41 Rogerio

    Use this

    SELECT REPLACE(column,'(888)','') FROM ABC; 
    

    Or use SUBSTRING

    SELECT SUBSTRING(column,6,15) FROM ABC; 
    

    Documentation:

    https://docs.microsoft.com/en-us/sql/t-sql/functions/substring-transact-sql

  • answered 2018-01-11 19:41 John Cappelletti

    Perhahaps a little overkill

    Example

    Declare @YourTable Table ([Phone] varchar(50))
    Insert Into @YourTable Values 
     ('(888) 477-8977x110')
    ,('(888) 477-8977 ext 110')
    ,('(888) 477-8977 extention 110')
    ,('(888) 477-8977 e 110')
    ,('888.477.8977ext110')
    ,('(888) 477-8977')
    
    
    Select * 
          ,Ext = ltrim(NullIf(reverse(ltrim(left(reverse(Phone),patindex('%[a-z]%',reverse(Phone)+'a')-1))),Phone))
     From  @YourTable
    

    Returns

    Phone                           Ext
    (888) 477-8977x110              110
    (888) 477-8977 ext 110          110
    (888) 477-8977 extention 110    110
    (888) 477-8977 e 110            110
    888.477.8977ext110              110
    (888) 477-8977                  NULL
    

  • answered 2018-01-11 19:41 Von Aaron

    Use Charindex and Substring.

                DECLARE @word VARCHAR(254) = '(888) 477-8977 ext 110'
                SELECT  IIF(CHARINDEX('ext', @word) > 0, LTRIM(RTRIM(SUBSTRING(@word, 0,
                                                                              LEN(@word)
                                                                              - LEN(SUBSTRING(@word,
                                                                              CHARINDEX('ext',
                                                                              @word),
                                                                              LEN(@word)))))), LTRIM(RTRIM(SUBSTRING(@word,
                                                                              0,
                                                                              LEN(@word)
                                                                              - LEN(SUBSTRING(@word,
                                                                              CHARINDEX('x',
                                                                              @word),
                                                                              LEN(@word)))))))
    

  • answered 2018-01-11 19:41 Shnugo

    If you have nothing more then x or ext you could take this road:

    DECLARE @tblPhoneNumber TABLE(ID INT IDENTITY, PhoneNumber VARCHAR(100));
    INSERT INTO @tblPhoneNumber VALUES
     ('(888) 477-8977x110')
    ,('(888) 477-8977 ext 110');
    
    SELECT LTRIM(RTRIM(
           CAST(
             '<x>' + REPLACE(REPLACE(PhoneNumber,'ext','x'),'x','</x><x>') + '</x>' AS XML
               ).value('/x[2]','nvarchar(max)')
           ))
    FROM @tblPhoneNumber;
    

    First all ext will be x , then all x will work as XML elements.
    The .value() picks the second element, which is the needed one.