Neither are working.
I have this function:
Create Function dbo.GetNumberic(@ItemDesc VarChar(8000))
Returns VarChar(8000)
AS
Begin
Return Left(
SubString(@ItemDesc, PatIndex('%[0-9]%', @ItemDesc), 8000),
PatIndex('%[^0-9]%', SubString(@ItemDesc, PatIndex('%[0-9]%', @ItemDesc), 8000))'X'-1)
End
and it extracts the numbers out of the alphanumeric field BUT the field is still a string and i need to match the numeric values to another table (membership number) and that's a numeric field.
So I tried the CONVERT (INT, dbo.GetNumeric(ItemDesc)) but it is not working. Nor is the CAST (dbo.GetNumeric(ItemDesc) AS INT).
Can you please help.