Check if a caracter exists in a sql field

[MrBaRRon]

Hi everybody ,


I would like with a Stored procedure check if a caracter exists in a sting stored in a SQL server field.

Example :


My field contains the caracters "ABCDEF" ,

I would like to check if "C" is in this string.

Tank U

 

[hmckillop]

There probably is some function already built in, but you could do it using SP as shown below

CREATE proc aa_test
(
@pi_instr VARCHAR(10),
@pi_SearchChar CHAR(1)
)
AS

BEGIN

DECLARE @v_Str VARCHAR(10)
DECLARE @v_found INT

SET @v_found = 0
SET @v_Str = @pi_Instr

WHILE len(@v_str) > 0 and @v_found = 0
BEGIN
IF LEFT(@v_Str, 1) = @pi_SearchChar
BEGIN
SET @v_found = 1
BREAK
END
ELSE
SET @v_Str = right(@v_str, len(@v_str) - 1)
END
SELECT @v_found
RETURN @v_found

END

Use it as follows:

exec aa_test 'STRMyst', 'x'

exec aa_test 'STRMyst', 'y'

 

[JamesLean]

Errr, there's a built-in function which is designed to do exactly what you need: CHARINDEX().

SELECT CHARINDEX('c', 'abcde')

This would return 3 (the position of the first string within the second).

--James

 

[hmckillop]

I thought there would be, but couldnt find it.