Function findPCR(pcode As String)

Dim startpos As Integer
Dim CurChar, sstring As String

startpos = 1
CurChar = "a"

Do While Not IsNumeric(CurChar) And startpos <= Len(pcode)
        CurChar = Mid(pcode, startpos, 1)
        startpos = startpos + 1
Loop

sstring = "%" & Mid(pcode, 1, startpos - 2) & "%"

If InStr(1, "%B%CV%DE%DY%LE%LN%NG%NN%PE%ST%SY%TF%WR%WS%WV%", sstring) > 0 Then
    findPCR = "Bob Thomas"
Else
    findPCR = "Unknown"
End If

End Function

-- This function will be used for getting the values from the deliminated. 

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CrackInRows]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[CrackInRows]
GO

CREATE FUNCTION [dbo].[CrackInRows] (@delim varchar(1), @Phrase2Crack as varchar(8000))
RETURNS @CrackRow table 
(
	INROWS varchar(1000)
)
as
BEGIN 
insert @CrackRow
Select NullIf(SubString(@Delim + @Phrase2Crack + @Delim , IDNos , 
CharIndex(@Delim , @Delim + @Phrase2Crack + @Delim , IDNos) - IDNos) , '') AS INROW
FROM IDNos
WHERE IDNos <= Len(@Delim + @Phrase2Crack + @Delim) AND 
SubString(@Delim + @Phrase2Crack + @Delim , IDNos - 1, 1) = @Delim 
AND CharIndex(@Delim , @Delim + @Phrase2Crack + @Delim , IDNos) - IDNos > 0
return
END
GO

-- Now Actualy function 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[findPCR]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[findPCR]
GO

CREATE Function findPCR( @pCode varchar(100))
Returns varchar(10)
As 
Begin 
Declare @pFindPCR varchar(10),
	@pFindString varchar(2000)

Set @pFindString = '%B%CV%DE%DY%LE%LN%NG%NN%PE%ST%SY%TF%WR%WS%WV%'

Select @pFindPCR = Case When Patindex('%[1-9]%',@pCode) > 0 Then 
			Left(@pCode,Patindex('%[1-9]%',@pCode)-1)
			Else 
			'' 
		   End 

if (@pFindPCR = '') 
	return 'Unknown'

If Exists (Select * From Dbo.CrackInRows('%',@pFindString) Where INROWS = @pFindPCR)
	return 'Bob Thomas'

return 'Unknown'

End 

GO
-- Call the function for testing .. 
Select Dbo.findPCR('CV8 6UJ')
Select Dbo.findPCR('C 6UJ')

quote:

---

Originally posted by harsh_athalye

CREATE FUNCTION dbo.findPCR
(
	@pcode varchar(2000)
)
returns Varchar(2000)
as
begin

declare @StartIndex int,
	@EndIndex int,
	@Result Varchar(2000), -- ","this was missing :-)
        @FinResult Varchar(200)

set @Result = ''

select @StartIndex = patindex('%[a-z][A-Z]%', @pcode),
	@EndIndex = PATINDEX('%[0-9]%',@pcode)-1

if @StartIndex > 0 and @EndIndex > 0
	select @Result = SubString(@pcode, @StartIndex, @EndIndex)

if PatIndex('%B%CV%DE%DY%LE%LN%NG%NN%PE%ST%SY%TF%WR%WS%WV%', @Result) > 0
	Select @FinResult = 'Bob Thomas'
else
	Select @FinResult = 'Unknown'

return @FinResult
end

-- select findPCR("CV8 6UJ") 

Harsh Athalye
India.
"Nothing is Impossible"

---

 	

Create FUNCTION dbo.findPCR_1 -- Harsh Version 
(
	@pcode varchar(2000)
)
returns Varchar(2000)
as
begin

declare @StartIndex int,
	@EndIndex int,
	@Result Varchar(2000),
        @FinResult Varchar(200)

set @Result = ''

select @StartIndex = patindex('%[a-z][A-Z]%', @pcode),
	@EndIndex = PATINDEX('%[0-9]%',@pcode)-1

if @StartIndex > 0 and @EndIndex > 0
	select @Result = SubString(@pcode, @StartIndex, @EndIndex)

return @result
if PatIndex('%'+@Result + '%','%B%CV%DE%DY%LE%LN%NG%NN%PE%ST%SY%TF%WR%WS%WV%') > 0
	Select @FinResult = 'Bob Thomas'
else
	Select @FinResult = 'Unknown'

return @FinResult
end

GO