Creating String With Special Characters

1

I wanted to trim down the volume of lines I have, so wanted to create a string that I could use many times in the statement.

CURRENTLY this works, but I have it many places:

                CASE WHEN Flags IN (
                '±4 sigma From Last Point',
                '2 Of 3 Points Outside ±2 sigma',
                'Downward Data Trend',
                'First Point Between ±2SD & ±3SD',
                'High Value Delta',
                'LOQ',
                'n Ascending/Descending Points',
                'n Consecutive Points',
                'n Points Inside ±1 sigma',
                'n Points Outside ±1 sigma',
                'n Points Outside ±2 sigma',
                'QC Negative 2SD',
                'QC Persuasive Consecutive Negative Bias',
                'QC Persuasive Consecutive Positive Bias',
                'QC Positive 2SD',
                'QC Value 4 Sigma Change',
                'Rate of Change',
                'UDL',
                'Upward Data Trend',
                'Warning Limit',
                'x Of y Points Outside ±2 sigma'
                ) THEN 1 ELSE 0 END AS Flags_New,

So, I am trying this to set a variable to call repeatedly, but the special character ± is causing havoc:

DECLARE @varFlags AS VARCHAR(MAX)
SET @varFlags = '
'±4 sigma From Last Point', 
'2 Of 3 Points Outside ±2 sigma', 
'Downward Data Trend', 
'First Point Between ±2SD & ±3SD', 
'High Value Delta', 
'LOQ', 
'n Ascending/Descending Points', 
'n Consecutive Points',  
'n Points Inside ±1 sigma', 
'n Points Outside ±1 sigma', 
'n Points Outside ±2 sigma', 
'QC Negative 2SD', 
'QC Persuasive Consecutive Negative Bias', 
'QC Persuasive Consecutive Positive Bias', 
'QC Positive 2SD', 
'QC Value 4 Sigma Change', 
'Rate of Change', 
'UDL', 
'Upward Data Trend', 
'Warning Limit', 
'x Of y Points Outside ±2 sigma''
2

hi

hope this helps

1st way
one idea is to create a temp table ... and use it everywhere

create temp table with Flags

drop table if exists #Flags
create table #Flags( Flag varchar(40))
insert into #Flags select '±4 sigma From Last Point'
insert into #Flags select '2 Of 3 Points Outside ±2 sigma'
insert into #Flags select 'Downward Data Trend'
insert into #Flags select 'First Point Between ±2SD & ±3SD'
insert into #Flags select 'High Value Delta'
insert into #Flags select 'LOQ'
insert into #Flags select 'n Ascending/Descending Points'
insert into #Flags select 'n Consecutive Points'
insert into #Flags select 'n Points Inside ±1 sigma'
insert into #Flags select 'n Points Outside ±1 sigma'
insert into #Flags select 'n Points Outside ±2 sigma'
insert into #Flags select 'QC Negative 2SD'
insert into #Flags select 'QC Persuasive Consecutive Negative Bias'
insert into #Flags select 'QC Persuasive Consecutive Positive Bias'
insert into #Flags select 'QC Positive 2SD'
insert into #Flags select 'QC Value 4 Sigma Change'
insert into #Flags select 'Rate of Change'
insert into #Flags select 'UDL'
insert into #Flags select 'Upward Data Trend'
insert into #Flags select 'Warning Limit'
insert into #Flags select 'x Of y Points Outside ±2 sigma'

SELECT
    case when a.Flags like '%'+ b.Flag + '%' then 1 else 0 end AS Flags_New 
FROM #Testing a , #Flags b

2nd way
Dynamic SQL

Create String Flags

DECLARE @varFlags AS NVARCHAR(MAX)
SET @varFlags = '(
''±4 sigma From Last Point'',
''2 Of 3 Points Outside ±2 sigma'',
''Downward Data Trend'',
''First Point Between ±2SD & ±3SD'',
''High Value Delta'',
''LOQ'',
''n Ascending/Descending Points'',
''n Consecutive Points'',
''n Points Inside ±1 sigma'',
''n Points Outside ±1 sigma'',
''n Points Outside ±2 sigma'',
''QC Negative 2SD'',
''QC Persuasive Consecutive Negative Bias'',
''QC Persuasive Consecutive Positive Bias'',
''QC Positive 2SD'',
''QC Value 4 Sigma Change'',
''Rate of Change'',
''UDL'',
''Upward Data Trend'',
''Warning Limit'',
''x Of y Points Outside ±2 sigma''
)'

declare @SQL nvarchar(max) = '' 
set @SQL = 'select case when  Flag IN '+@varFlags + ' then 1 else 0 end   from #Flags' 
EXEC sp_executesql  @SQL
3

hi

hope this helps

this could be your issue

double quotes instead of single quotes

DECLARE @varFlags AS VARCHAR(MAX)
SET @varFlags = '
''±4 sigma From Last Point'', 
''2 Of 3 Points Outside ±2 sigma'', 
''Downward Data Trend'', 
''First Point Between ±2SD & ±3SD'', 
''High Value Delta'', 
''LOQ'', 
''n Ascending/Descending Points'', 
''n Consecutive Points'',  
''n Points Inside ±1 sigma'', 
''n Points Outside ±1 sigma'', 
''n Points Outside ±2 sigma'', 
''QC Negative 2SD'', 
''QC Persuasive Consecutive Negative Bias'', 
''QC Persuasive Consecutive Positive Bias'', 
''QC Positive 2SD'', 
''QC Value 4 Sigma Change'', 
''Rate of Change'', 
''UDL'', 
''Upward Data Trend'', 
''Warning Limit'', 
''x Of y Points Outside ±2 sigma''
'
4

If the idea here is to do IN (@varFlags) - it isn't going to work. No matter how you create the string - it is still a single string and IN expects separate and distinct values for each item.

If you want to simplify your coding - put that data into a table and then you can use IN (SELECT item FROM yourVarFlags).

Other options - use a view or function with a VALUES table constructor to return the list of items. Downside of this approach is that you need to update the view and/or function every time a new value needs to be included.

If you can identify these values programmatically - then a view or function can be written so new items are automatically included.