Extracting Characters in SQL SERVER 2005?

Question

What is the best way of extracting the values from vvv.www.xxx.yyy.zzz

Note that vvv or www or xxx or yyy or zzz can vary i.e. it can be of any length.

For this reason I cannot use substring with charindex.

I don't want to do this with LOOP or CURSOR . By using CTE and a number table also it can be done but that will be a bit lengthy program.

Any simple 1 line program is there for accomplishing the same.

The desired output will be

Col1 Col2 Col3 Col4 Col5

vvv  www  xxx   yyy   zzz

It is our assignment

Thanks

Answer 1

You can use SUBSTRING with CHARINDEX, it just takes a bit more work:

declare @s as varchar(25)
set @s = 'vvv.www.xxx.yyy.zzz'
select 
left(@s, charindex('.', @s) - 1) as col1,
substring(@s, charindex('.', @s) + 1, charindex('.', @s, charindex('.', @s) + 1) - charindex('.', @s) - 1) as col2,
substring(@s, charindex('.', @s, charindex('.', @s) + 1) + 1, charindex('.', @s, charindex('.', @s, charindex('.', @s) + 1) + 1) - charindex('.', @s, charindex('.', @s) + 1) - 1) as col3,
substring(@s, charindex('.', @s, charindex('.', @s, charindex('.', @s) + 1) + 1) + 1, charindex('.', @s, charindex('.', @s, charindex('.', @s, charindex('.', @s) + 1) + 1) + 1) - charindex('.', @s, charindex('.', @s, charindex('.', @s) + 1) + 1) - 1) as col4, 
right(@s, len(@s) - charindex('.', @s, charindex('.', @s, charindex('.', @s, charindex('.', @s) + 1) + 1) + 1)) as col5

You could clean it up with a join like this:

declare @s as varchar(25)
set @s = 'vvv.www.xxx.yyy.zzz'
select left(@s, first - 1) as col1,
    substring(@s, first + 1, second - first - 1) as col2,
    substring(@s, second + 1, third - second - 1) as col3,
    substring(@s, third + 1, fourth - third - 1) as col4,
    right(@s, len(@s) - fourth)
from (select @s s) a
inner join
(
    select charindex('.', @s) as first,
    charindex('.', @s, charindex('.', @s) + 1) as second,
    charindex('.', @s, charindex('.', @s, charindex('.', @s) + 1) + 1) as third,
    charindex('.', @s, charindex('.', @s, charindex('.', @s, charindex('.', @s) + 1) + 1) + 1) as fourth
) b on 1=1

---