0

i want to create a stored procedure that can return the initial character of two string inside the description field.
ex. Computer Security -> in one data only

if i use this statement
select left(description) from table1

i want to return only CS when i run my script.

is there a way to do this?


thanks alot

2
Contributors
2
Replies
3
Views
5 Years
Discussion Span
Last Post by cyberdaemon
0

Yes, although I think you'd be better off writing it as a recursive function, since it does no updating and returns a single literal value.

Not only that, but you can embed it into select statements for any table (even for multiple columns in the same select statement) and not have to worry about having to retrieve your result and THEN call this stored proc.

Anyway, here are examples of each implementation.

First, as a stored proc:

create procedure dbo.usp_Initials
(@myDesc as varchar(50))
as
begin
declare @inits varchar(50)
declare @pos int
select @pos = 1, @inits = ''
while @pos <= LEN(@myDesc)
  begin
    select @inits = @inits + substring(@myDesc, @pos, 1)
    select @pos = CHARINDEX(' ', @myDesc, @pos + 1)
    if @pos = 0 
      begin
        break
      end
    set @pos = @pos + 1
  end
select @inits
end

Call it like this:

exec dbo.usp_initials @myDesc = 'Computer Science'

Now as a function:

create function dbo.fn_Initials
(@myDesc as varchar(50))
returns varchar(50)
as
begin
declare @Inits varchar(50)
declare @pos int
select @Inits = left(@myDesc, 1), @pos = CHARINDEX(' ', @myDesc, 0) 
if @pos > 0
  begin
    select @inits = @inits + dbo.fn_Initials(substring(@myDesc, CHARINDEX(' ', @myDesc, 0) + 1, len(@myDesc)))
  end
return @inits
end

Call it like this:

select dbo.fn_Initials('Computer Science')

Or, if you have a table to select from:

select myDesc1, 
dbo.fn_Initials(myDesc1) as myDesc1Inits, 
myDesc2,
dbo.fn_Initials(myDesc2) as myDesc2Inits
from dbo.mydescriptionfun

Hope these are helpful. Good luck!
p.s. I hope this wasn't a homework assignment, because solving it was fun.

0

thank you for answering my question.. i helps to solve my problem... More power.. and HAPPY CODING..

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.