NAME CHECK PROCEDURE

--For checking name duplicacy/re-application during new recruitment
DECLARE @string char(75),
@output1 varchar(1000),
@First   VARCHAR(25),
@Middle  VARCHAR(25),
@Last    VARCHAR(25),
@NCHECK  VARCHAR(75),
@NCHECK2 VARCHAR(75),
@NCHECK3 VARCHAR(75)

CREATE TABLE #NAME_CHECK
(
First    VARCHAR(25)        NULL,
Middle  VARCHAR(25)        NULL,
Last    VARCHAR(25)        NULL,
Cname   VARCHAR(75)        NULL,
Asc_val VARCHAR(1000)   NULL
)
SET NOCOUNT ON
INSERT INTO #NAME_CHECK(First, Middle, Last)

SELECT ISNULL(H01_First_Name,''), ISNULL(H01_Middle_Name,''), ISNULL(H01_Last_Name,'')
FROM H01_identfcatn
WHERE H01_emp_num = '00002'

UPDATE #NAME_CHECK
SET Cname = UPPER(LTRIM(RTRIM(ISNULL(First,'')+''+ISNULL(Middle,'')+''+ISNULL(Last,''))))
FROM #NAME_CHECK

SELECT @string = Cname,
@First = First,
@Middle = Middle,
@Last = Last
FROM #NAME_CHECK

SET @output1 = ''

SELECT @output1 = @output1 + CAST(ASCII(SUBSTRING(@string, number, 1)) as varchar) + '+'
FROM master..spt_values
WHERE TYPE = 'p' and number between 1 and LEN(@string)
ORDER BY number

UPDATE #NAME_CHECK
SET Asc_val = @output1

SELECT * FROM #NAME_CHECK
SET @NCHECK  = 'KUMARAKHILESHJHA'
SET @NCHECK2 = 'JHAKUMARAKHILESH'
SET @NCHECK3 = 'JHAKOMAARAKHILESH'

--Looking for similarity in names--Name
--@NCHECK
SELECT Cname AS 'SOUNDEX'
FROM #NAME_CHECK
WHERE SOUNDEX(@NCHECK) = SOUNDEX(Cname)

SELECT Cname ,@NCHECK, DIFFERENCE(Cname,@NCHECK) AS 'Difference'
FROM #NAME_CHECK
WHERE DIFFERENCE(Cname,@NCHECK)>=2

--@NCHECK2
SELECT Cname AS 'SOUNDEX'
FROM #NAME_CHECK
WHERE SOUNDEX(@NCHECK2) = SOUNDEX(Cname)

SELECT Cname ,@NCHECK2, DIFFERENCE(Cname,@NCHECK2) AS 'Difference'
FROM #NAME_CHECK
WHERE DIFFERENCE(Cname,@NCHECK2)>=2

--@NCHECK3
SELECT Cname AS 'SOUNDEX'
FROM #NAME_CHECK
WHERE SOUNDEX(@NCHECK3) = SOUNDEX(Cname)

SELECT Cname ,@NCHECK3, DIFFERENCE(Cname,@NCHECK3) AS 'Difference'
FROM #NAME_CHECK
WHERE DIFFERENCE(Cname,@NCHECK3)>=2

DROP TABLE #NAME_CHECK
------------------------------------------------------------------------------------




---- EXEC SALARY_FITMENT_SOM2 'KSLTDJMD','KSSD','1','Somnath  Das'
--- grant exec on SALARY_FITMENT_SOM2 to public  
ALTER PROC SALARY_FITMENT_SOM2             
(             
@C03_Organisation_code GLCOMPANY,             
@C29_Location_Code GLLOCN,             
@lang_id      int,             
@NAME VARCHAR(100)
)
AS
BEGIN

CREATE Table #ASCII_TEXT
(
Position INT,
Letter varchar(1),
Letter_Val INT,
Sl INT
)

CREATE Table #ASCII_final
(
NAME VARCHAR(100) null
)

Declare @Name_U varchar(100),
        @Name_T varchar(100)

Select @Name_U = (Select UPPER(@NAME))
Set @Name_T = ''


DECLARE @position int,
        @Letter_Val INT,
        @Sl1 int

SET @position = 1
WHILE @position <= DATALENGTH(@Name_U)
BEGIN
INSERT INTO #ASCII_TEXT(Position,Letter,Letter_Val)
SELECT @position,CHAR(ASCII(SUBSTRING(@Name_U, @position, 1))),ASCII(SUBSTRING(@Name_U, @position, 1))

Select @Letter_Val = (Select ASCII(SUBSTRING(@Name_U, @position, 1)))

IF(@position = 1 and @Letter_Val = 32)
BEGIN
Set @Sl1 = 0
END

ELSE IF(@position = 1 and @Letter_Val <> 32)
BEGIN
Set @Sl1 = 1
END

ELSE IF(@position > 1 and @Letter_Val = 32)
BEGIN
Set @Sl1 = 0
END

ELSE IF(@position > 1 and @Letter_Val <> 32)
BEGIN
Set @Sl1 = (Select Sl from #ASCII_TEXT where Position = @position-1)+1
END

UPDATE #ASCII_TEXT
Set Sl = @Sl1
Where Position = @position

IF(@Letter_Val <> 32)
BEGIN
Select @Name_T = (Select @Name_T+CHAR(ASCII(SUBSTRING(@Name_U, @position, 1))))
END

SET @position = @position + 1
END


Select @Name_U
Select * from #ASCII_TEXT
Select @Name_T
END