switching address with Subtring & len funtion

  • I have something out of order what is it I'm not sure, I want to

    reverse the field(s) Doe.John@CompanyABC.com to

    John.Doe@CompanyABC.com

    but my code: select SUBSTRING(Email, CHARINDEX('.', Email + 1, LEN(d Email)) + '.' + SUBSTRING(Email, -1, CHARINDEX('.', Email) +1)

    from tbl$

    gives me John@CompanyABC.com.Doe

  • Whatever you have posted cannot be compiled!

    Here what you can do:

    select SUBSTRING(Email, -1, CHARINDEX('.', Email) +1) +

    SUBSTRING(Email, CHARINDEX('.', Email), LEN(Email))

    from ...

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • the code you posted just give the lastname and company.com (doe@CompanyABC.com). I'm here Doe.John@CompanyABC.com And I want to get to John.Doe@CompanyABC.com

  • kd11 (11/20/2012)


    the code you posted just give the lastname and company.com (doe@CompanyABC.com). I'm here Doe.John@CompanyABC.com And I want to get to John.Doe@CompanyABC.com

    Not true. I've tested it:

    declare @email varchar(100) = 'Doe.John@CompanyABC.com'

    select SUBSTRING(@Email, -1, CHARINDEX('.', @Email) +1) +

    SUBSTRING(@Email, CHARINDEX('.', @Email), LEN(@Email))

    returns:

    ------------------------------------

    Doe.John@CompanyABC.com

    (1 row(s) affected)

    What are you using? SQL2008?

    You may try another version:

    declare @email varchar(100) = 'Doe.John@CompanyABC.com'

    select SUBSTRING(@Email, 0, CHARINDEX('.', @Email)) +

    SUBSTRING(@Email, CHARINDEX('.', @Email), LEN(@Email))

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • However, I've realised it doesn't change the name places...

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • declare @email varchar(100) = 'Doe.John@CompanyABC.com'

    select SUBSTRING(@Email, CHARINDEX('.', @Email)+1,CHARINDEX('@', @Email)-CHARINDEX('.', @Email)-1)

    + '.'

    + SUBSTRING(@Email, 0, CHARINDEX('.', @Email))

    + SUBSTRING(@Email, CHARINDEX('@', @Email),LEN(@Email))

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Another one from Lynn Pettis, for some reason he couldn't post it himself...

    declare @email varchar(100) = 'Doe.John@CompanyABC.com'

    SELECT

    SUBSTRING(@email, 1, CHARINDEX('.', @email) - 1),

    SUBSTRING(@email, CHARINDEX('.', @email) + 1, CHARINDEX('@', @email) - CHARINDEX('.', @email) - 1),

    RIGHT(@email,LEN(@email) - CHARINDEX('@', @email) + 1),

    SUBSTRING(@email, CHARINDEX('.', @email) + 1, CHARINDEX('@', @email) - CHARINDEX('.', @email) - 1) + '.' +

    SUBSTRING(@email, 1, CHARINDEX('.', @email) - 1) +

    RIGHT(@email,LEN(@email) - CHARINDEX('@', @email) + 1),

    @email

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • select

    PARSENAME(left(email, charindex('@', email) - 1), 1) + '.' +

    PARSENAME(left(email, charindex('@', email) - 1), 2) +

    substring(email, charindex('@', email), len(email))

    from (

    select 'Doe.John@CompanyABC.com' as email

    ) as test_table

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • ScottPletcher (11/20/2012)


    select PARSENAME(left(email, charindex('@', email) - 1), 1) + '.' + PARSENAME(left(email, charindex('@', email) - 1), 2) +

    substring(email, charindex('@', email), len(email))

    from (

    select 'Doe.John@CompanyABC.com' as email

    ) as test_table

    Just to note: PARSENAME is elegant but not the best performer...

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Eugene Elutin (11/20/2012)


    ScottPletcher (11/20/2012)


    select PARSENAME(left(email, charindex('@', email) - 1), 1) + '.' + PARSENAME(left(email, charindex('@', email) - 1), 2) +

    substring(email, charindex('@', email), len(email))

    from (

    select 'Doe.John@CompanyABC.com' as email

    ) as test_table

    Just to note: PARSENAME is elegant but not the best performer...

    Odd ... this article's testing showed the opposite:

    http://www.sqlservercentral.com/articles/IPv4+Addresses/67534/

    "

    To eliminate deviations caused by table lookups, I looped each method 10,000 times on the same VARCHAR(15) IPv4 address. I performed the test 10 times to make sure the results were consistent.

    SUBSTRING and CHARINDEX 3334 ms

    PARSENAME ....................... 3325 ms

    Improved SUBSTRING and CHARINDEX 3332 ms

    Improved PARSENAME ........................ 3323 ms

    It is very clear that there is little difference in performance between the different methods. However, it is also evident that PARSENAME is faster than multiple SUBSTRING and CHARINDEX calls.

    "

    [emphasis added]

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • ScottPletcher (11/20/2012)


    Eugene Elutin (11/20/2012)


    ScottPletcher (11/20/2012)


    select PARSENAME(left(email, charindex('@', email) - 1), 1) + '.' + PARSENAME(left(email, charindex('@', email) - 1), 2) +

    substring(email, charindex('@', email), len(email))

    from (

    select 'Doe.John@CompanyABC.com' as email

    ) as test_table

    Just to note: PARSENAME is elegant but not the best performer...

    I don't think it was really due to use of CHARINDEX and SUBSTRING, it was more to do with REVERSE, which is not too fast one in T-SQL. However I can see the need for a dedicated test here...

    Odd ... this article's testing showed the opposite:

    http://www.sqlservercentral.com/articles/IPv4+Addresses/67534/

    "

    To eliminate deviations caused by table lookups, I looped each method 10,000 times on the same VARCHAR(15) IPv4 address. I performed the test 10 times to make sure the results were consistent.

    SUBSTRING and CHARINDEX 3334 ms

    PARSENAME ....................... 3325 ms

    Improved SUBSTRING and CHARINDEX 3332 ms

    Improved PARSENAME ........................ 3323 ms

    It is very clear that there is little difference in performance between the different methods. However, it is also evident that PARSENAME is faster than multiple SUBSTRING and CHARINDEX calls.

    "

    [emphasis added]

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • After a bit of testing, I can confirm that PARSENAME does win much more often over use of CHARINDEX with SUBSTRING's than other way around.

    (Will keep this result in my head now, looks like I had a wrong assumption from somewhere...)

    Mostly the difference is negligible, but still I would declare PARSENAME to be a winner in my little competition.

    There is actually one way to increase performance slightly, using CROSS APPLY to calculates position of '@':

    select @var = PARSENAME(left(email, AT - 1), 1) + '.' +

    PARSENAME(left(email, AT - 1), 2) +

    SUBSTRING(email, AT, len(email))

    FROM #TestTable

    CROSS APPLY (SELECT CHARINDEX('@', Email)) P(AT)

    Now, I'm eager to create CLR one...;-)

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • oops, double post...

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Eugene Elutin (11/20/2012)

    After a bit of testing, I can confirm that PARSENAME does win much more often over use of CHARINDEX with SUBSTRING's than other way around.

    (Will keep this result in my head now, looks like I had a wrong assumption from somewhere...)

    Mostly the difference is negligible, but still I would declare PARSENAME to be a winner in my little competition.

    Not to mention being vastly more readable and instantly understandable :-). It takes a while to determine what a long string of CHARINDEX/SUBSTRING/whatever is doing.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Boys, boys. If you're going to argue about performance may I interject a new dog into this race?

    DECLARE @Email VARCHAR(100) = 'John.Doe@companyabc.com'

    SELECT Email=RIGHT(

    STUFF(@Email

    ,CHARINDEX('@', @Email)

    ,1, '.' + LEFT(@Email, CHARINDEX('.', @Email)-1) + '@')

    ,LEN(@Email))

    Here's a test harness (hope I selected the right code for you both):

    DECLARE @Email VARCHAR(100)

    CREATE TABLE #Emails (email VARCHAR(100))

    ;WITH Tally (n) AS (

    SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

    FROM sys.all_columns a CROSS JOIN sys.all_columns b)

    INSERT INTO #Emails

    SELECT 'doe' + CAST(n AS VARCHAR(7)) + '.John ' + CAST(n AS VARCHAR(7)) + '@CompanyABC.com'

    FROM Tally

    PRINT 'Eugene Eletin'

    SET STATISTICS TIME ON

    select @Email = SUBSTRING(email, CHARINDEX('.', email)+1,CHARINDEX('@', email)-CHARINDEX('.', email)-1)

    + '.'

    + SUBSTRING(email, 0, CHARINDEX('.', email))

    + SUBSTRING(email, CHARINDEX('@', email),LEN(email))

    FROM #Emails

    SET STATISTICS TIME OFF

    PRINT 'Scott Pletcher'

    SET STATISTICS TIME ON

    select @Email=

    PARSENAME(left(email, charindex('@', email) - 1), 1) + '.' +

    PARSENAME(left(email, charindex('@', email) - 1), 2) +

    substring(email, charindex('@', email), len(email))

    FROM #Emails

    SET STATISTICS TIME OFF

    PRINT 'Dwain.C'

    SET STATISTICS TIME ON

    SELECT @Email=RIGHT(

    STUFF(email

    ,CHARINDEX('@', email)

    ,1, '.' + LEFT(email, CHARINDEX('.', email)-1) + '@')

    ,LEN(email))

    FROM #Emails

    SET STATISTICS TIME OFF

    DROP TABLE #Emails

    And some results:

    (1000000 row(s) affected)

    Eugene Eletin

    SQL Server Execution Times:

    CPU time = 2294 ms, elapsed time = 2318 ms.

    Scott Pletcher

    SQL Server Execution Times:

    CPU time = 2761 ms, elapsed time = 2810 ms.

    Dwain.C

    SQL Server Execution Times:

    CPU time = 1295 ms, elapsed time = 1306 ms.

    Woof!


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

Viewing 15 posts - 1 through 15 (of 35 total)

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