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

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.

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.