How to make "AND" and "&" working as same in SQL

Pinku

I have a script which is accepting "Black AND White" but Idf do like "Black & White" its returning like "Black White" . How to make it work for bot AND and & in sql server. below is the script.

DECLARE @Title varchar(255)='Black AND White'
DECLARE @RawDescription varchar(255) =@Title

WHILE PATINDEX('%[^ ''0-9A-Za-z]%', @RawDescription) > 0
BEGIN
IF SUBSTRING(@RawDescription, PATINDEX('%[^ ''0-9A-Za-z]%', @RawDescription), 1) = '-'
BEGIN
SET @RawDescription = TRIM(REPLACE(@RawDescription, SUBSTRING(@RawDescription, PATINDEX('%[^ ''0-9A-Za-z]%', @RawDescription), 1), ' '))
END
ELSE
BEGIN
SET @RawDescription = TRIM(REPLACE(@RawDescription, SUBSTRING(@RawDescription, PATINDEX('%[^ ''0-9A-Za-z]%', @RawDescription), 1), ''))
END
END
WHILE CHARINDEX(' ', @RawDescription) > 0
BEGIN
SET @RawDescription = REPLACE(@RawDescription, ' ', ' ')
END

select LTRIM(RTRIM(@RawDescription))

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jayraj Chhaya1y ago

Accepted answer

Hi Pinku,

To achieve the desired functionality where both "AND" and "&" are treated the same in your SQL script, you can modify the existing script to replace "&" with "AND" before processing the string. This way, both terms will be handled uniformly. Below is the revised version of your script:

DECLARE @Title varchar(255)='Black & White'
DECLARE @RawDescription varchar(255) = REPLACE(@Title, '&', 'AND')

WHILE PATINDEX('%[^ ''0-9A-Za-z]%', @RawDescription) > 0
BEGIN
    IF SUBSTRING(@RawDescription, PATINDEX('%[^ ''0-9A-Za-z]%', @RawDescription), 1) = '-'
    BEGIN
        SET @RawDescription = TRIM(REPLACE(@RawDescription, SUBSTRING(@RawDescription, PATINDEX('%[^ ''0-9A-Za-z]%', @RawDescription), 1), ' '))
    END
    ELSE
    BEGIN
        SET @RawDescription = TRIM(REPLACE(@RawDescription, SUBSTRING(@RawDescription, PATINDEX('%[^ ''0-9A-Za-z]%', @RawDescription), 1), ''))
    END
END

WHILE CHARINDEX('  ', @RawDescription) > 0
BEGIN
    SET @RawDescription = REPLACE(@RawDescription, '  ', ' ')
END

SELECT LTRIM(RTRIM(@RawDescription))

In this updated script, the line DECLARE @RawDescription varchar(255) = REPLACE(@Title, '&', 'AND') ensures that any occurrence of "&" in the input string is replaced with "AND" before further processing. This allows your script to handle both terms seamlessly.

Jignesh Kumar1y ago

Hello Rinku,
Please this script which will work for you,

DECLARE @Title varchar(255) = 'Black AND White'
DECLARE @RawDescription varchar(255) = @Title


SET @RawDescription = REPLACE(@RawDescription, '&', '&&')

SET @RawDescription = REPLACE(@RawDescription, SUBSTRING(@RawDescription, PATINDEX('%[^a-zA-Z0-9 ]%', @RawDescription), 1), '')

SET @RawDescription = REPLACE(@RawDescription, '&&', '&')


SET @RawDescription = REPLACE(@RawDescription, '  ', ' ')


SELECT LTRIM(RTRIM(@RawDescription))

Sangeetha S1y ago

Hi,

SQL script accept both "AND" and "&" as separators in string

DECLARE @Title varchar(255) = 'Black & White'
DECLARE @RawDescription varchar(255) = @Title

-- Replace '&' with 'AND'
SET @RawDescription = REPLACE(@RawDescription, '&', 'AND')

-- Continue with your existing logic
WHILE PATINDEX('%[^ ''0-9A-Za-z]%', @RawDescription) > 0
BEGIN
    IF SUBSTRING(@RawDescription, PATINDEX('%[^ ''0-9A-Za-z]%', @RawDescription), 1) = '-'
    BEGIN
        SET @RawDescription = TRIM(REPLACE(@RawDescription, SUBSTRING(@RawDescription, PATINDEX('%[^ ''0-9A-Za-z]%', @RawDescription), 1), ' '))
    END
    ELSE
    BEGIN
        SET @RawDescription = TRIM(REPLACE(@RawDescription, SUBSTRING(@RawDescription, PATINDEX('%[^ ''0-9A-Za-z]%', @RawDescription), 1), ''))
    END
END

WHILE CHARINDEX('  ', @RawDescription) > 0
BEGIN
    SET @RawDescription = REPLACE(@RawDescription, '  ', ' ')
END

SELECT LTRIM(RTRIM(@RawDescription))

Replace '&' with 'AND':

SET @RawDescription = REPLACE(@RawDescription, '&', 'AND')