In Microsoft SQL, the SUBSTRING allows you extract part of a string using a start index and length. For example:
SELECT SUBSTRING('hello world', 2, 3)
Results in an output of ‘ell’.
However, there is no ability to select the start and end of the sub-string using known letters or words.
The SQL example shown below gets round this problem, and since it is a function you can used in queries.
GO
/****** Object: UserDefinedFunction [dbo].[SubstringByText] Script Date: 08/03/2018 10:37:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Dominic Rawle
-- Create date: 2018-03-08
-- Description: Get block of text using strings rather than integers
-- =============================================
CREATE FUNCTION [dbo].[SubstringByText]
(
-- Add the parameters for the function here
@WholeText NVARCHAR(MAX)
, @StartText NVARCHAR(MAX)
, @EndText NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
-- Declare the return variable here
DECLARE @Result NVARCHAR(MAX)
-- No need to change anything beyond this point!
DECLARE @StartIndex INT = CHARINDEX(@StartText, @WholeText);
DECLARE @EndIndex INT = (CHARINDEX(@EndText, @WholeText, @StartIndex));
-- Condition protects us from returning all data if indexes are not found, which could be very slow!
IF(@StartIndex > 0 AND @EndIndex > 0)
BEGIN
SET @Result = SUBSTRING(@WholeText
, @StartIndex
, (@EndIndex - @StartIndex) + LEN(@EndText)
);
END ELSE
BEGIN
SET @Result = NULL;
END
-- Return the result of the function
RETURN @Result
END
For example, it could be used (whilst probably not the best solution) to extract certain node values from an XML string.
<xmldata>
<color>green</color>
</xmldata>
SELECT dbo.substringbytext('<xmldata><color>green</color></xmldata>','<color>','</color>')
Post Categories