April 22, 2013 at 2:25 pm
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:
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.
April 22, 2013 at 2:30 pm
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.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 22, 2013 at 2:32 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 22, 2013 at 2:35 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 22, 2013 at 2:49 pm
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.
April 22, 2013 at 3:18 pm
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
April 22, 2013 at 3:25 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 22, 2013 at 4:25 pm
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.
April 23, 2013 at 8:16 am
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.
April 23, 2013 at 8:26 am
Son of a gun, it did not occur to me to use a RIGHT inside the charindex. That's pretty sweet.
Thanks, Geoff.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply