ProTech-Online.com

Microsoft SQL function code example to parse a Syteline serial number.

USE [testsite_live_App]
GO
/****** Object:  UserDefinedFunction [MYSCHEMA].[_chad_parse_serial_number]    Script Date: 06/24/2013 23:39:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [MYSCHEMA].[_chad_parse_serial_number] (
  @SerialNumber nvarchar(30))
RETURNS nvarchar(30)
AS
BEGIN
      DECLARE @TMPSTRING2  AS NVARCHAR(MAX)
      DECLARE @START AS INT
      DECLARE @LENGHT AS INT
      SET @TMPSTRING2 = SUBSTRING(REVERSE(@SerialNumber),0,PATINDEX('%0-%', REVERSE(@SerialNumber))+1)
      SET @SerialNumber = LEFT(@SerialNumber, LEN(@SerialNumber) - LEN(@TMPSTRING2))
      SET @TMPSTRING2 = REVERSE(@TMPSTRING2)
      SET @START = PATINDEX('%[1-9]%', @TMPSTRING2)
      SET @LENGHT = LEN(@TMPSTRING2) - @START
      SET @TMPSTRING2 = SUBSTRING(@TMPSTRING2,@START, LEN(@TMPSTRING2) - @LENGHT)
      RETURN      @SerialNumber + @TMPSTRING2

END