Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

switching address with Subtring & len funtion Expand / Collapse
Author
Message
Posted Tuesday, November 20, 2012 8:04 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 12:26 PM
Points: 143, Visits: 1,131
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
Post #1386934
Posted Tuesday, November 20, 2012 8:19 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:38 AM
Points: 2,858, Visits: 5,132
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1386944
Posted Tuesday, November 20, 2012 8:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 12:26 PM
Points: 143, Visits: 1,131
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
Post #1386953
Posted Tuesday, November 20, 2012 8:43 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:38 AM
Points: 2,858, Visits: 5,132
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1386967
Posted Tuesday, November 20, 2012 8:45 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:38 AM
Points: 2,858, Visits: 5,132
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1386970
Posted Tuesday, November 20, 2012 8:52 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:38 AM
Points: 2,858, Visits: 5,132



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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1386976
Posted Tuesday, November 20, 2012 9:29 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:38 AM
Points: 2,858, Visits: 5,132
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1387002
Posted Tuesday, November 20, 2012 9:33 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:27 PM
Points: 2,127, Visits: 3,216

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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1387004
Posted Tuesday, November 20, 2012 9:34 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 9:38 AM
Points: 2,858, Visits: 5,132
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1387005
Posted Tuesday, November 20, 2012 9:55 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:27 PM
Points: 2,127, Visits: 3,216
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1387018
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse