none
SQL Help RRS feed

  • Question

  • Hello All,

    I have one column values like below. I need renaming values after special character @. Which function is suitable for this case.

    Example: 

    1234@78900000

    12@9999999

    12345678@1000000

    Final Output:

    78900000

    9999999

    1000000

    Thanks,

    CMK

     

    Friday, July 19, 2019 4:38 PM

All replies

  • One way

    Declare @Sample Table(MyData varchar(20));
    Insert @Sample(MyData) Values
    ('1234@78900000'),
    ('12@9999999'),
    ('12345678@1000000');
    
    Select Substring(MyData, CharIndex('@', MyData) + 1, Len(MyData))
    From @Sample;
    

    Tom

    Friday, July 19, 2019 5:11 PM
  • Hi cmk1,

    Here is another way:

    DECLARE @Sample TABLE(MyData VARCHAR(20));
    INSERT @Sample(MyData) VALUES
    	('1234@78900000'),
    	('12@9999999'),
    	('12345678@1000000');
    
    SELECT Mydata AS [Before], PARSENAME(REPLACE(MyData,'@','.'), 1) AS [After]
    FROM @Sample;

    Output:

    Before	After
    1234@78900000	78900000
    12@9999999	9999999
    12345678@1000000	1000000

    Friday, July 19, 2019 5:36 PM
  • Select stuff(Col,1 , CharIndex('@', Col),'')
    From yourtable;

    Friday, July 19, 2019 5:50 PM
    Moderator
  • declare @V varchar(50)
    set @V = '1234@78900000'
    
    select SUBSTRING(@V,charindex('@',@V) + 1,len(@V))

    Friday, July 19, 2019 7:42 PM
  • create table #temp (col1 varchar(200))
    insert into #temp values ('1234@78900000'),('12@9999999'),('12345678@1000000')

     select right(col1, len(col1) - charindex('@',col1))
     from #temp
    Saturday, July 20, 2019 8:03 AM
  • Hi cmk1,

    Here it is: 

    create table test ( 
    col varchar(50))
    go
    insert into test values
    ('1234@78900000'),
    ('12@9999999'),
    ('12345678@1000000') 
    
    
    select reverse (substring(reverse(col),1, CHARINDEX('@',reverse(col))-1))
    from test 
    
    /*
    ------------
    78900000
    9999999
    1000000
    */

    Sabrina


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, July 22, 2019 1:33 AM