Recently I had a need to extract the number from a string in the database. \u00a0The string would be something like ‘Monitor 16″‘ or “16 inch Monitor”. \u00a0 I would need to get the size for various reasons including fees and reports, but no real good way of doing it. \u00a0I have come up with the following Scalar SQL Function to do this very job.<\/p>\n
<\/p>\n
SET ANSI_NULLS ON\r\nGO\r\nSET QUOTED_IDENTIFIER ON\r\nGO\r\n-- =============================================\r\n-- Author:\t\tAndrew Pallant\r\n-- Create date: August 27, 2013\r\n-- Description:\tGet Numeric Value Prior to a String within a String.\r\n-- =============================================\r\nCREATE FUNCTION f_read_num_before_some_string \r\n(\r\n\t-- Add the parameters for the function here\r\n\t@stringValue varchar(max),\r\n\t@search varchar(max)\r\n)\r\nRETURNS varchar(100)\r\nAS\r\nBEGIN\r\n\t-- Declare the return variable here\r\nDECLARE @intval as varchar(100) -- Value to be returned\r\nDECLARE @RECORDINGVALUE bit -- Flag to indicate the recording of the value to be returned\r\ndeclare @newvalue as varchar(100) -- Working String Value\r\n\r\n-- Set Defaults\r\nset @newvalue = '' \r\nset @RECORDINGVALUE = 0\r\nset @intval = null\r\n\r\n-- Start Looping and Building\t\r\n\tIF len(isnull(@stringValue,'')) > 0 BEGIN\r\n\t\tdeclare @startIndex as int\r\n\t\tset @startIndex = CHARINDEX(@search,@stringValue)\r\n\r\n\t\tdeclare @counter int\r\n\t\tset @counter = @startIndex\r\n\r\n\t\tWHILE @counter > 0 BEGIN\r\n\t\t\tdeclare @temp as varchar(1)\r\n\t\t\tset @temp = substring(@stringValue,@counter,1)\r\n\t\t\tif @temp = ' ' and LEN(@newvalue)> 0 BEGIN\r\n\t\t\t\t-- STOP - Drop and Roll\r\n\t\t\t\tset @counter = -1\r\n\t\t\tEND \r\n\r\n\t\t\t-- If we have a space - do work!\r\n\t\t\tIF @temp <> ' ' BEGIN\r\n\t\t\t\tIF @RECORDINGVALUE = 1 and isnumeric(@temp) = 0 BEGIN\r\n\t\t\t\t\tset @counter = -1\r\n\t\t\t\t\t-- STOP - Drop and Roll\r\n\t\t\t\tEND ELSE IF isnumeric(@temp) = 1 BEGIN\r\n\t\t\t\t\tset @RECORDINGVALUE = 1\r\n\r\n\t\t\t\t\t-- Build Return Value\r\n\t\t\t\t\tset @newvalue = @temp + @newvalue;\r\n\t\t\t\tEND\r\n\t\t\tEND \r\n\r\n\t\t\tset @counter = @counter - 1\r\n\t\tEND\r\n\tEND\r\n\r\n\tIF @newvalue = '' BEGIN SET @newvalue = '0' END\r\n\r\n\t-- Return the result of the function\r\n\tRETURN @newvalue\r\nEND\r\nGO<\/pre>\n","protected":false},"excerpt":{"rendered":"