Question About using the replace() function

[jpower69]

I am using sql server 5 and reporting services

I am trying to add one space into a string that comes from a filter in report services...

My filter is the following; the user can select multiple days of the week, say MON and Fri...the filter displays the
fields as MON,FRI...
the field in the table that I am trying to select the data from has the days as MON, FRI...one space between the xomma and the next day...

what i want to do is insert a space for every occurance in the string so that i can retrieve the data correctly...
my code is as follows..;;
if (charindex('-2',@days) = 0 ) and (charindex('-3',@days) = 0)
begin
declare @index int
declare @totallength as int
declare @txt varchar(max)
select replace(@days,',',', ')
set @index = charindex(',',@days)
set @totallength = len(@days)
set @txt = substring(@days,1, @totallength)

-- Select Chosen Day(s)
-- set @wheredays = 'And day in ('''+substring(@txt,1,len(@txt)+1)+''')'
-- set @wheredays = 'AND star.ConcatenatePreferences(pa.ParticipantApplicationID, ''DAYS'') in ('''+substring(@txt,1,len(@txt)+1)+''')'
set @wheredays = 'AND day in ('''+substring(@txt,1,len(@txt)+1)+''')'

i read about using the replace() to replace all occurances of one character in a string..however, when i run the above code, it does not insert a space betweenj the comma and FRI for example...
can anyone tell me what i'm doing wrong here...or is there a better way to perform the insert...

 

[bborissov]

REPLACE() is a function and it returns value, you should store this value in some variable to use it.
REPLACE() didn't change the value of the @days it just returns changed string. So simply using SELECT REPLACE(...) wouldn work. Try htis:
[/code]
....
declare @txt varchar(max)
SET @days = replace(@days,',',', ') -- NOT select replace(@days,',',', ')
set @index = charindex(',',@days)
....
[/code]

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[bborissov]

And becuase I hate that code is not in code block:

....
declare @txt varchar(max)
SET @days = replace(@days,',',', ') -- NOT select replace(@days,',',', ')
set @index = charindex(',',@days)
....

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[jpower69]

thank you....really appreciate the help...

 

[jpower69]

tahnsk again...worked perfectly...

appreciate the help...

again thanks

 

[jpower69]

Question....can you point me in the right direction for eitehr a book(s) or articles that area available for symtx rules, etc...as from the above, it appears that even thought i followed the example in the Beginning T-SQL book,
your statements worked...for instance
the following code works find ;
if (charindex('-2',@days)<> 0) and (charindex('-3',@days )= 0)
begin
-- Select ALL Days--
set @wheredays = ''
end


however, if I change the statement charindex('02', etc
to the following charindex('Se', etc nothing is return..

so i was wnodering if there are books/articles that i can look at to show the different syntax rules for statmes like above...

thanks again for all you help...