How can I parse special characters in T SQL

Question

I'm writing an SQL Server function which should parse the input string and apply some function on it, some characters in the input are " and '. How can I recognize them in function?

Declare @Str varchar(300)
Declare @CurrentChar char    --("A" OR "B") AND "C"

DECLARE @POSITION INT

SET @POSITION = 0
DECLARE @FLAG INT
SET @FLAG= 0

declare @colName varchar(15)
declare @SearchKeyWord varchar(200)
set @colName='article_title'
set @SearchKeyWord='"A" and "B"'

WHILE @POSITION <LEN(@SearchKeyWord) 
    BEGIN

        SET @CurrentChar =  SUBSTRING(@SearchKeyWord,@POSITION+1,@POSITION+2)
        print 'CurrentChar ' +  @CurrentChar
*       if @CurrentChar = ('"')
            BEGIN
                IF (@FLAG=0) 
                    BEGIN
                        SET @FLAG=1;
                        SET @Str = @Str + @colName + ' LIKE ''%';   
                    END 
                ELSE --FLAG=1 : end of the parsing word
                    BEGIN
                        SET @FLAG = 0 ;
                        SET @Str = @Str + '%'' ';   
                    END

            print 'str: ' +  @Str
            END
        if (@CurrentChar = (' ') OR @CurrentChar = ('(') OR @CurrentChar = (')') OR     (ASCII(@CurrentChar) BETWEEN 65 and 90) OR (ASCII(@CurrentChar) BETWEEN 97 and 122) OR (ASCII(@CurrentChar) BETWEEN 48 and 57))
            BEGIN
                    --print 'else'
                    SET @Str = @Str + @CurrentChar ;    

            print 'str: ' +  @Str
            END


        SET @POSITION = @POSITION + 1
    END

Answer 1

Rather than single quote in all special characters, at Sqlserver side, no issue you will be face.

The only thing with single quote, whenever you have single quote, you can replace with double single quote. So sql will execute with a single quote.

Declare @Str varchar(300)
Declare @CurrentChar char    --("A" OR "B") AND "C"

DECLARE @POSITION INT = 0

DECLARE @FLAG INT = 0

declare @colName varchar(15) = 'article_title'
declare @SearchKeyWord varchar(200) = '"A" ''and "B"' --I added here the single quote(') and its works


WHILE @POSITION <LEN(@SearchKeyWord) 
    BEGIN

        SET @CurrentChar =  SUBSTRING(@SearchKeyWord,@POSITION+1,@POSITION+2)
        print 'CurrentChar ' +  @CurrentChar
        if @CurrentChar = ('"')
            BEGIN
                IF (@FLAG=0) 
                    BEGIN
                        SET @FLAG=1;
                        SET @Str = @Str + @colName + ' LIKE ''%';   
                    END 
                ELSE --FLAG=1 : end of the parsing word
                    BEGIN
                        SET @FLAG = 0 ;
                        SET @Str = @Str + '%'' ';   
                    END

            print 'str: ' +  @Str
            END
        if (@CurrentChar = (' ') OR @CurrentChar = ('(') OR @CurrentChar = (')') OR     (ASCII(@CurrentChar) BETWEEN 65 and 90) OR (ASCII(@CurrentChar) BETWEEN 97 and 122) OR (ASCII(@CurrentChar) BETWEEN 48 and 57))
            BEGIN
                    --print 'else'
                    SET @Str = @Str + @CurrentChar ;    

            print 'str: ' +  @Str
            END


        SET @POSITION = @POSITION + 1
    END

Here I give some explanation while working with C# code and Sql procedure .

--****suppose you want to check via sp, ****--
--alter procedure get_thedataWithParameterValueHaveSpecialChar
--(
declare @stringParameter Varchar(50) --suppose you passing the value from c# is : "a'b\"c"
print @stringParameter --will show at 
--)
--as
--begin
declare @find nvarchar(500), @replace nvarchar(500)
    Set @find =''+ char(39) +''  --this is the ascii value of single quote            
    Set @replace = ''+char(39)+char(39)+''

    Set @stringParameter =  '"'+ replace(@stringParameter,@find,@replace)  +'*"'                
print @stringParameter --will show the double single quote

--end   

Answer 2

Escape char for single quot (') is single quot (') itself. For double quot (") you can use it as it is.

Try This

DECLARE @sStr varchar(10)

SET @sStr = ''''

If @sStr = ('''')
    SELECT 'Str is '''
ELSE IF @sStr = ('"')
    SELECT 'Str is "'
ELSE 
    SELECT 'Other char'

Answer 3

My Problem was something else

I should Set the Str before entering to While loop

set @Str = '';

:)

Answer 4

CREATE TABLE SPECIAL_CH (S_CH VARCHAR(10))
INSERT INTO SPECIAL_CH
VALUES (''''),(';'),('"'),('<')

CREATE TABLE USER_TABLE (ID INT identity primary key,STRING VARCHAR(50))

INSERT INTO USER_TABLE
VALUES ('nothingin2'),('nothingin3'),('"charhere'),('charhere<'),('nothingin')

/*Multiple chars in string*/
INSERT INTO USER_TABLE
VALUES ('charshere<"')

/* USE [YourDB]
   GO SET ANSI_NULLS ON
   GO SET QUOTED_IDENTIFIER ON
   GO

ALTER FUNCTION dbo.FindS_CH (@input VARCHAR(255))
returns VARCHAR(255)
AS BEGIN

DECLARE @RETURN VARCHAR(255)
SET @RETURN = (SELECT  x.RESULT
                    FROM
                    (SELECT @input AS STRING) p
                    CROSS APPLY (select 
REPLACE(stuff((select ', ' + cast(S_CH as varchar(512))
        from SPECIAL_CH c 
        where p.STRING LIKE '%'+ c.S_CH +'%' 
        for xml path('')),1,2,''),'&lt','') as RESULT) x)

RETURN @RETURN
END
END*/




SELECT ID,STRING ,dbo.FindS_CH(STRING)
FROM USER_TABLE