asked on

SQL Function

Hi, I have the following code from a dll which our sql function calls to join Names. This Dll is part of SQL assembly. We would like stop using the assembly and instead use this code within the function. Is there way to convery this to SQL script.

HERE IS HOW Current SQL function calls the EXTERNAL assembly

ALTER FUNCTION [dbo].[JoinName](@FirstName [nvarchar](80), @MiddleName [nvarchar](80), @LastName [nvarchar](80), @Suffix [nvarchar](80))
RETURNS [nvarchar](330) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [JoinName].[JoinName.UserDefinedFunctions].[JoinName]

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Partial Public Class UserDefinedFunctions
    Private Const MULTIPLE_SPACE_PATTERN As String = "\s{2,}"
    Private Const SINGLE_SPACE_REPLACEMENT As String = " "
    Private Const TRAILING_COMMA_PATTERN As String = "\,$"
    Private Const EMPTY_STRING_REPLACEMENT As String = ""
    Private Const LAST_NAME_PRESENT_FORMAT As String = "{0}, {1} {2} {3}"
    Private Const LAST_NAME_ABSENT_FORMAT As String = "{0} {1} {2}"
    <Microsoft.SqlServer.Server.SqlFunction(DataAccess:=DataAccessKind.None, _
                                            SystemDataAccess:=SystemDataAccessKind.None, _
                                            IsDeterministic:=True, _
                                            IsPrecise:=True)> _
    Public Shared Function JoinName(ByVal FirstName As SqlString, _
                                    ByVal MiddleName As SqlString, _
                                    ByVal LastName As SqlString, _
                                    ByVal Suffix As SqlString) As SqlString

        Dim JoinedName As SqlString
        Dim JoinedNameAsString As String

        If FirstName.IsNull Then
            FirstName = ""
        End If

        If MiddleName.IsNull Then
            MiddleName = ""
        End If

        If LastName.IsNull Then
            LastName = ""
        End If

        If Suffix.IsNull Then
            Suffix = ""
        End If

        If (LastName <> "") Then
            JoinedNameAsString = String.Format(LAST_NAME_PRESENT_FORMAT, LastName, FirstName, MiddleName, Suffix)
        Else
            JoinedNameAsString = String.Format(LAST_NAME_ABSENT_FORMAT, FirstName, MiddleName, Suffix)
        End If

        JoinedName = Trim(System.Text.RegularExpressions.Regex.Replace(input:=JoinedNameAsString, _
                                                                       pattern:=MULTIPLE_SPACE_PATTERN, _
                                                                       replacement:=SINGLE_SPACE_REPLACEMENT, _
                                                                       options:=Text.RegularExpressions.RegexOptions.IgnoreCase))


        JoinedName = Trim(System.Text.RegularExpressions.Regex.Replace(input:=JoinedName.ToString, _
                                                                       pattern:=TRAILING_COMMA_PATTERN, _
                                                                       replacement:=EMPTY_STRING_REPLACEMENT, _
                                                                       options:=Text.RegularExpressions.RegexOptions.IgnoreCase))

        Return JoinedName
    End Function

Open in new window

HainKurt

what does this function do? any sample?

vb7guy

ASKER

This Function Joins Name (formal Name) and returns a string. For example
if I pass the following name to the function
Firstname: TOBIN LastName: SMITH MiddleName: L

it should return
SMITH, TOBIN L

Also, if any of the passing field has null value or spaces it also truncates the spaces

HainKurt

create a simple function like this and use it in your sql

alter function JoinName(
 @FirstName Varchar(100),
 @MiddleName Varchar(100),
 @LastName Varchar(100),
 @Suffix Varchar(100)
) returns varchar(400) as
begin
  declare @Result Varchar(400) ='';
    --Private Const LAST_NAME_PRESENT_FORMAT As String = "{0}, {1} {2} {3}"
    --Private Const LAST_NAME_ABSENT_FORMAT As String = "{0} {1} {2}"
 
  if @LastName = ''
    set @result = @Firstname + ' ' + @MiddleName + ' ' + @Suffix;
  else
    set @result = @Lastname + ', ' + @Firstname + ' ' + @MiddleName + ' ' + @Suffix;
  
  return RTrim(LTrim(Replace(@Result, '  ', ' ')));
end;


select dbo.JoinName('Hain', 'Kurt', '', 'Mr.')
Hain Kurt Mr.

Open in new window

ASKER CERTIFIED SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

vb7guy

ASKER

Answer was partial. but I figured out the rest