Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


nested left and right query?


nested left and right query?

Author
Message
sqldriver
sqldriver
SSChasing Mays
SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)

Group: General Forum Members
Points: 606 Visits: 2492
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.
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5715 Visits: 7660
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
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16667 Visits: 17030
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)
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16667 Visits: 17030
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)
sqldriver
sqldriver
SSChasing Mays
SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)

Group: General Forum Members
Points: 606 Visits: 2492
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.
sqldriver
sqldriver
SSChasing Mays
SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)

Group: General Forum Members
Points: 606 Visits: 2492
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


Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16667 Visits: 17030
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)
sqldriver
sqldriver
SSChasing Mays
SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)

Group: General Forum Members
Points: 606 Visits: 2492
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.
geoff5
geoff5
SSC-Enthusiastic
SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)

Group: General Forum Members
Points: 188 Visits: 543
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.
sqldriver
sqldriver
SSChasing Mays
SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)SSChasing Mays (606 reputation)

Group: General Forum Members
Points: 606 Visits: 2492
Son of a gun, it did not occur to me to use a RIGHT inside the charindex. That's pretty sweet.

Thanks, Geoff.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search