DECLARE @Str NVARCHAR( -)= '1_bb_cc_dd_aa_ee_yy_ww_hh_gg'--//1. Gets the number of times an underscore appears in a stringSELECT LEN(@Str)- LEN(REPLACE(@Str,'_',"'))--//2. Get the subscript where the underline appearsIF EXISTS(SELECT * fromSys.objectsWHERE object_id = object_id(N'[dbo]. [Indexnumofstr]') andTypeinch(N'FN'N'IF'N'TF'N'FS'N'FT')) DROP FUNCTION [dbo]. IndexnumofstrGOCREATE FUNCTIONdbo. Indexnumofstr (@Str NVARCHAR( -),--string @charStr NVARCHAR( -),--the character to query @num INT --The first few occurrences)RETURNS INT asBEGIN DECLARE @posIndex INT = 0 --the location that appears DECLARE @i INT = 0 --Number of lookups while @i < @num BEGIN SET @i = @i + 1 SET @posIndex = CHARINDEX(@charStr,@Str,@posIndex + 1) IF @posIndex = 0 BEGIN RETURN 0 END END RETURN @posIndexENDGO--//test ResultsSELECTDbo. INDEXNUMOFSTR (@Str,'_',Ten)
SQL Server Learning Series-Gets the number of occurrences of a character in a string and the subscript at which a character appears