Solved

SQL Function

Posted on 2013-05-10
3
378 Views
Last Modified: 2013-05-10
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.
0
Comment
Question by:sspb485
3 Comments
 
LVL 39

Expert Comment

by:Kyle Abrahams
ID: 39156238
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
 
LVL 31

Accepted Solution

by:
awking00 earned 500 total points
ID: 39156257
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
 

Author Comment

by:sspb485
ID: 39156340
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now