SQL Function

Hi,

I have a column name that contains values like
http://myServer/Lists/myform/DispForm.aspx?ID=6, RAB12EGH234
http://myServer/Lists/myform/DispForm.aspx?ID=5, CBTRR2345

I just need to get the formName which is the last string value after, and space.

I have used this to get the desired result:
rtrim(substring(FormName, charindex(', ', FormName, 0) + 2, 50))

Now I want to create a SQL function where I could pass in that Column Name and get the desired result. How do I do that? TIA.
sspb485Asked:
Who is Participating?
 
awking00Connect With a Mentor Commented:
CREATE FUNCTION [dbo].[getFormName](@p_column varchar)
RETURNS VARCHAR(MAX)
AS
BEGIN
declare @FormName varchar(max)
set @FormName = rtrim(substring(@p_column,charindex(', ',@p_column, 0)+2,50))
RETURN @FormName;
END;

select dbo.getFormName(yourcolumn)
from yourtable;
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
declare @test varchar(100)
declare @index int
set @test = 'http://myServer/Lists/myform/DispForm.aspx?ID=5, CBTRR2345'

set @index = CHARINDEX(' ', @test)

select SUBSTRING(@test, @index + 1, len(@test) - @index)



@test would be the column value you're passing in.
0
 
sspb485Author Commented:
Hi awking00,

I did create the function however it returned blank when I did select dbo.getFormName(yourcolumn)
from yourtable;

Any clue? Thanks.

Nvm, I found the mistake. There's no length of varchar for @p_column. Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.