I have a requirement in my project

one column is getting values like

sdfjsdlj.kfjdj.abc.bombay.wue
ikekeke.kfjdj.abc.pune.uro
woeek.kfjdj.lmn.chennai.mno
oflfk.kfjdj.jky.hydrabad.xyz
eiodkk.woek.skssfsd.delhi.dfsids
I want to derive another column which should have value of the fourth
block( out of bombay,pune,chennai,hydrabad) if third block value is
"abc"

so in this case of my expected outpout would be
derived column
-----------------------
bombay
pune
---------------------

On Apr 14, 8:12*pm, sandeep <springbolt.... (AT) gmail (DOT) com> wrote:
Hi

I think that there is no easy way to implement what you are looking
for. If your ASE version is 15.0.2 or greater, you can use a T-SQL UDF
function (see an example below). If T-SQL UDFs are not available then
you can implement the same logic using nested calls to system
functions - which is quite cumbersome.

Given said all that, I think that if extracting the n-th component
from a field is not an one-time task, you probably should consider
some redesign and store relevant string components in separate fields
from the beginning.
In addition, you can bcp out the data and it bcp the resulted file
into a new table using the dot as a delimiter.

create table tb
(
var_str varchar(100) not null
)
go

insert into tb values ('sdfjsdlj.kfjdj.abc.bombay.wue')
insert into tb values ('ikekeke.kfjdj.abc.pune.uro')
insert into tb values ('woeek.kfjdj.lmn.chennai.mno')
insert into tb values ('oflfk.kfjdj.jky.hydrabad.xyz')
insert into tb values ('eiodkk.woek.skssfsd.delhi.dfsids')
go

drop function get_delimited_component
go
create function get_delimited_component
( @p_str varchar(255),
@p_delimiter varchar(4),
@p_component_number tinyint
)
returns varchar(255)
as
begin
declare @l_delimiter_counter tinyint, @l_str varchar(255),
@l_charindex tinyint
select @l_delimiter_counter = 1, @l_str = @p_str
while @l_delimiter_counter <= @p_component_number - 1
begin
select @l_charindex = charindex(@p_delimiter, @l_str)
if @l_charindex = 0
begin
return ''
end
select @l_str = right(@l_str, char_length(@l_str) -
@l_charindex)
select @l_delimiter_counter = @l_delimiter_counter + 1
end
select @l_charindex = charindex(@p_delimiter, @l_str)
if @l_charindex = 0
begin
return @l_str
end
select @l_str = left(@l_str, @l_charindex - 1)
return @l_str
end
go

select dbo.get_delimited_component(var_str, '.', 4)
from tb
where var_str like '%.%.abc.%.%'
go

Hope it helps
Leonid Gvirtz
http://www.gvirtz-consulting.com