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))
declare @inits varchar(50)
declare @pos int
select @pos = 1, @inits = ''
while @pos <= LEN(@myDesc)
    select @inits = @inits + substring(@myDesc, @pos, 1)
    select @pos = CHARINDEX(' ', @myDesc, @pos + 1)
    if @pos = 0 
    set @pos = @pos + 1
select @inits

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)
declare @Inits varchar(50)
declare @pos int
select @Inits = left(@myDesc, 1), @pos = CHARINDEX(' ', @myDesc, 0) 
if @pos > 0
    select @inits = @inits + dbo.fn_Initials(substring(@myDesc, CHARINDEX(' ', @myDesc, 0) + 1, len(@myDesc)))
return @inits

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, 
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 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.