locked
Help in query..plz assist RRS feed

  • Question

  • User483994611 posted

    I have a email string like 'aa@gmail.com'

    i want to make generalized query to  prepend a value before @ 

    so that output will look like 'aa12@gmail.com

    if the input is bb@gmail.com output will be bb12@gmail.com

    some emails have already 12 appended before @ i want to add this condition also

    what ever the input email will be  it will return a defined number before @

    if CHARINDEX('12',@emailString) > 0
    begin
    append 12 before @ in email id 
    end
    else
    do nothing 

    Thursday, September 13, 2018 8:33 PM

All replies

  • User-1716253493 posted

    REPLACE ([email], '@', '12' + '@')

    Friday, September 14, 2018 1:07 AM
  • User2103319870 posted

    You can use STUFF function in sql server to append "12' before @ symbol like below

    If you want to add 12 only when value 12 is not present in string then you can use below code

    SELECT CASE WHEN CHARINDEX('12',EmailString) > 0 THEN EmailString ELSE  STUFF(EmailString,CHARINDEX('@',EmailString),0,'12') END as FormattedString FROM @TABLE
    

    However if you want add value 12 always you can try with below code

    SELECT STUFF(EmailString,CHARINDEX('@',EmailString),0,'12') as FormattedString FROM @TABLE
    
    Friday, September 14, 2018 1:20 AM
  • User483994611 posted

    I want to add one more case condition..but i am getting error  Incorrect syntax near '>'.

    the value 12 and email will be dynamic in my script probably a user defined function will be helpful

    Plz suggest

    Declare @EmailString varchar(200)
    set @EmailString='aa@gmail.com'
    SELECT
    CASE @EmailString
    WHEN 'mm@gmail.com' THEN @EmailString
    WHEN (CHARINDEX('12',@EmailString) > 0) THEN @EmailString
    ELSE STUFF(@EmailString,CHARINDEX('@',@EmailString),0,'12')
    END

    Friday, September 14, 2018 5:04 AM
  • User-1718098015 posted

    Hi 

    Please try this below function should help 

    Create function udf_AdjustEmail(@EmailAddress varchar(500), @appendValue varchar(10))
    returns varchar(500)
    as
    begin
    		if CHARINDEX('@', @EmailAddress) = 0 --validate if there is @ or not 
    		return @EmailAddress
    		
    		SELECT @EmailAddress = 
    			CASE WHEN CHARINDEX(@appendValue,@EmailAddress) > 0 
    					THEN @EmailAddress 
    				ELSE  
    					STUFF(@EmailAddress,CHARINDEX('@',@EmailAddress),0,@appendValue) 
    			END 
    		return @EmailAddress
    end
    GO

    To use this function see below code, 

    select dbo.udf_AdjustEmail('aa@gmail.com', '12')

    Thanks
    Nirav 

    Friday, September 14, 2018 8:30 AM
  • User77042963 posted

    SELECT
    CASE
    WHEN @EmailString='mm@gmail.com' THEN @EmailString
    WHEN (CHARINDEX('12',@EmailString) > 0) THEN @EmailString
    ELSE STUFF(@EmailString,CHARINDEX('@',@EmailString),0,'12') END

    --or

    SELECT
    CASE
    WHEN @EmailString='mm@gmail.com' or (CHARINDEX('12',@EmailString) > 0) THEN @EmailString
    ELSE STUFF(@EmailString,CHARINDEX('@',@EmailString),0,'12') END

    Friday, September 14, 2018 1:45 PM