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

nested left and right query? Expand / Collapse
Author
Message
Posted Monday, April 22, 2013 2:25 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 4:42 PM
Points: 424, Visits: 1,666
Is it possible to nest (or perhaps stack via CTE) left and right?

For example, if i had a list of websites in different formats like:

http://www.google.com
https://www.google.com
www.google.com
www.google.info
www.google.mobi
www.google.de

And I just wanted to pull out google from each of them, using left and right with charindex on '.'

I spent a little bit of time messing with it, but couldn't work out the kinks. It's mostly just a curiosity thing, I don't have a task that requires it. I understand that there's probably a better way to do this.


Post #1445170
Posted Monday, April 22, 2013 2:30 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:10 PM
Points: 6,237, Visits: 7,392
Easiest way is to use the REVERSE function to trim the ends off each side, in case there are .'s in the middle. Otherwise you use a nested set of CHARINDEX() functions, using one as the 'start position' to find the second one.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1445176
Posted Monday, April 22, 2013 2:32 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:04 PM
Points: 13,296, Visits: 12,147
Use PARSENAME.

with MyData(url) as
(
select 'http://www.google.com' union all
select 'https://www.google.com' union all
select 'www.google.com' union all
select 'www.google.info' union all
select 'www.google.mobi' union all
select 'www.google.de'
)

select PARSENAME(url, 2)
from MyData



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1445179
Posted Monday, April 22, 2013 2:35 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:04 PM
Points: 13,296, Visits: 12,147
This will still work as long as there aren't more than 3 '.' in your value.

So things like 'http://www.maps.google.com' will work fine.

But 'http://www.mysubdomain.yourdomain.anotherdomain.com' will return NULL.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1445181
Posted Monday, April 22, 2013 2:49 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 4:42 PM
Points: 424, Visits: 1,666
Sean Lange (4/22/2013)
This will still work as long as there aren't more than 3 '.' in your value.

So things like 'http://www.maps.google.com' will work fine.

But 'http://www.mysubdomain.yourdomain.anotherdomain.com' will return NULL.


That is pretty spiffy. Thank you.

But... is there a way to nest left and right? What if I wanted it from an email address where the first charindex would like be '@'? It looks like PARSENAME only works on periods.

Something like

select left(right(len(email) - charindex('@', email), email)len(email) - charindex('.', email))

Except, that works. Because that doesn't work. Heh.

Post #1445190
Posted Monday, April 22, 2013 3:18 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 4:42 PM
Points: 424, Visits: 1,666
Well, this gets me the very end of an email. Not exactly what I was looking for. Oh well.

with rt as (
select right(email, len(email) - charindex('@', email)) as [rtemail]
from el_table
), rt2 as (
select right(rtemail, len(rtemail) - charindex('.', rtemail)) as [rt2email]
from rt
)
select rt2email
from rt2
where charindex('.', rt2email) = 0

Post #1445201
Posted Monday, April 22, 2013 3:25 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:04 PM
Points: 13,296, Visits: 12,147
erikd (4/22/2013)
Well, this gets me the very end of an email. Not exactly what I was looking for. Oh well.

with rt as (
select right(email, len(email) - charindex('@', email)) as [rtemail]
from el_table
), rt2 as (
select right(rtemail, len(rtemail) - charindex('.', rtemail)) as [rt2email]
from rt
)
select rt2email
from rt2
where charindex('.', rt2email) = 0



PASRENAME does do it's separation on periods but that doesn't mean you can't use nested replaces to utilize it. Perhaps if you can post some sample data and what you are trying to extract I can help. Your requirements changed quite a bit from the first post (looking for domain names) to your last post (trying to extract email addresses).


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1445205
Posted Monday, April 22, 2013 4:25 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 4:42 PM
Points: 424, Visits: 1,666
I know, sorry about that. Got distracted thinking about a more probable use for it. I did write something with parsename, which is very helpful and probably smarter than messing with left and right, though it also just captures the .com/net whatever part of the email reliably under specific conditions.

https://www.dropbox.com/s/y7pcvk1i7h7h9wb/emailx.xlsx

I replaced all letters and numbers with Xs, which for some reason took me a really long time to write, even cheating with Excel. So you can kind of see what I'm working with, as far as there being a lot of '.'s in my data. Some of the email addresses are a malformed nightmare, btw.

I guess my original thought was "cool nested left/right thing to whittle down data to what I want", but my brain drifted right back to the awful email validation case statement I had to mess with this weekend, over here. So now I'm wondering if it's applicable to that in any way, though I think not.

Post #1445229
Posted Tuesday, April 23, 2013 8:16 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Thursday, August 21, 2014 7:49 PM
Points: 171, Visits: 501
I read the thread from your original post, and so far the emphasis has been on finding alternatives to LEFT and RIGHT, but your original question was just about the ability to nest LEFT and RIGHT to achieve one particular kind of result. So I just wanted to answer your actual question, and the answer is unequivocally "yes, you can nest LEFT and RIGHT." It may not be as flexible as the other "solutions" offered, but it can be done and if your data is rigid enough to have only two delimiters in it -- two periods, say, or one @ and one period -- then it can do exactly what you describe.

I'm going to "steal" the MyData CTE text from the earlier post in the thread, but then I'd like to share two solutions: one for extracting a domain from a URL and one for extracting a domain from an email address. The "trick," if there is one, is that you have to use the calculation of one of the options twice inside the second one. In the examples I use the RIGHT calculate twice inside the LEFT calculation: once to stand in for the primary "string" to be manipulated by the LEFT function, and a second time as the string to be manipulated by the CHARINDEX() function to locate the second delimiter.

Here is the solution for URL, using a period as the delimiter for both left and right:

with MyData(url) as
(
select 'http://www.google.com' union all
select 'https://www.google.com' union all
select 'www.google.com' union all
select 'www.google.info' union all
select 'www.google.mobi' union all
select 'www.google.de'
)

select
url,
left(right(url, len(url) - charindex('.', url)), charindex('.', right(url, len(url) - charindex('.', url)))-1) as extract
from MyData;

And here is the solution for the email address, using @ as the delimiter on the left and a period as the delimiter on the right:

with MyData(email) as
(
select 'an.email@google.com' union all
select 'another.email@google.com' union all
select 'yetathirdemail@google.com' union all
select 'fourthemail@google.info' union all
select 'fifthtest@google.mobi' union all
select 'sixth@google.de'
)

select
email,
left(right(email, len(email) - charindex('@', email)), charindex('.', right(email, len(email) - charindex('@', email)))-1) as extract
from MyData

Hope this helps address your original question, and perhaps assist with your secondary concern about extracting well-formed information out of malformed email addresses.
Post #1445456
Posted Tuesday, April 23, 2013 8:26 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 4:42 PM
Points: 424, Visits: 1,666
Son of a gun, it did not occur to me to use a RIGHT inside the charindex. That's pretty sweet.

Thanks, Geoff.
Post #1445467
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse