0

I am using SQL Server 2008. Is there a way I can create computed column (deterministic) by extracting only numerical value from another alphanumeric column in the same table? and then index it?

For example, in my table tblVendorShippingInfo I have a column PONum what is alphanumeric, data type VARCHAR.

I tried to create a function and added the column (below)

ALTER FUNCTION [dbo].[Calc_GetPOID] (@strText VARCHAR(100))
RETURNS INT
AS
BEGIN
    WHILE PATINDEX('%[^0-9]%', Left(@strText, 8) ) > 0
    BEGIN   
        SET @strText = STUFF(@strText, PATINDEX('%[^0-9]%', @strText), 1, '')
    END
    RETURN Left(@strText,8)
END

-- Add the computed column 
ALTER TABLE tblVendorShippingInfo 
    ADD POID AS dbo.[Calc_GetPOID](PONum)

When I tried to add an index for POID computed column, it prompted error something related to "cannot add because it is non-deterministic column"...

Any suggestions to make it deterministic and indexable?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Milacay
  • 1,407
  • 7
  • 32
  • 56
  • 1
    Are the "alpha" guaranteed to be Latin alphabet. If so you could use 26 nested replaces rather than looking for all non numeric in a loop in a scalar UDF. Or you could just add `SCHEMABINDING` to your existing function. – Martin Smith May 03 '17 at 20:41
  • It is not always Latin aphabet, sometime it contains special characters like `,$,&,@,!,',",+....`. So if I use `SCHEMABINDING`, I should be able to index that column? Thanks for the response. – Milacay May 03 '17 at 21:07
  • 1
    Yep. Your function doesn't access any data or use non deterministic functions so it will be recognised as deterministic as here http://rextester.com/USM49231 – Martin Smith May 03 '17 at 21:08
  • It works. Thank you so much for helping. – Milacay May 03 '17 at 21:14

0 Answers0