Help tranlating values and Nulls

Hi,
I'm appending selections from 2 tables together and putting the results in a
3rd table. Some of the columns in the tables can be NULL or ' '. I would
like to convert anything that is not 0-9,a-z,A-Z in all positions into
something, say "~".
I'm new to SQL Server. I've tried a Case statement, an IF statement and
can't seem to get it to work. I've used LIKE '[0-z]' in both of those and i
n
the few cases that I didn't get errors, it just didn't work.
Can anyone help me with this?
Thanks,
ArtWe can help you if you could at least post some sample data, preferably with
DDL, and specify expected results.
So far you've specified that any value which is not a letter, be it a upper
or lowercase character, or a number should be transformed to the tilde
character "~".
In other words: if the column in question contains any data other than
letters and numbers its value must be replaced by the "~" character.
Or is it like this: any character other than letters and numbers are to be
replaced by the "~" character.
See the confusion? Or is it just me...?
ML|||ML,
Thanks for getting back to me. What I want to do is:
Look at a value from a particular column. If any character in the value is
not [0-z] then replace the entire value by ~.
My purpose in this is that the incoming data is a mixture of valid values,
empty strings and nulls -- so far. I have finally found a way of fixing the
NULLs. I don't know for sure what else I might find in that column. So
rather than look for NULLs or empty strings and adjusting them, I thought it
might be better to look for anything that isn't valid.
Thanks,
Art
"ML" wrote:

> We can help you if you could at least post some sample data, preferably wi
th
> DDL, and specify expected results.
> So far you've specified that any value which is not a letter, be it a uppe
r
> or lowercase character, or a number should be transformed to the tilde
> character "~".
> In other words: if the column in question contains any data other than
> letters and numbers its value must be replaced by the "~" character.
> Or is it like this: any character other than letters and numbers are to be
> replaced by the "~" character.
> See the confusion? Or is it just me...?
>
> ML|||Good data starts with validation. :)
A CASE function will take care of what you need:
case
when <column> like '%[^A-Za-z0-9]%' or <column> = ''
then '~'
else isnull(<column>, '~')
end
Includes handling null values.
ML|||ML,
Thanks for the help.
Art
"ML" wrote:

> Good data starts with validation. :)
> A CASE function will take care of what you need:
> case
> when <column> like '%[^A-Za-z0-9]%' or <column> = ''
> then '~'
> else isnull(<column>, '~')
> end
> Includes handling null values.
>
> ML

Help regarding Parameters selections in reporting services 2005.

Hi Experts,
I am working on Reporting services 2005 and i am new for this
software.
I have some reports, and some reports contains more than one
parameters.
Now my problem starts...
My client want reports with more and more parameters, and
at the same time he does not want to select all of them
He is saying that he may or may not select all the parameters.
and if not select all the parameters then still reports should be
generated.
for his selected parameters only.
Suppose i have one report with 4 parameters and first parameter is
Country
second one is states, third one is city, fourth one coustmers name
etc.
How i can implement this.
Now he is saying that if he select only one parameters in country and
leave others,
then report should be generated for all the coustemers for that
country.
Please help me...
Regards
DineshCheck this thread:
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/43cddb294583ab2c/dc7146eb95e741bb?lnk=gst&q=all+union+parameter&rnum=3#dc7146eb95e741bb
Hope it helps.
On Mar 30, 1:44 pm, "Dinesh" <dinesh...@.gmail.com> wrote:
> Hi Experts,
> I am working on Reporting services 2005 and i am new for this
> software.
> I have some reports, and some reports contains more than one
> parameters.
> Now my problem starts...
> My client want reports with more and more parameters, and
> at the same time he does not want to select all of them
> He is saying that he may or may not select all the parameters.
> and if not select all the parameters then still reports should be
> generated.
> for his selected parameters only.
> Suppose i have one report with 4 parameters and first parameter is
> Country
> second one is states, third one is city, fourth one coustmers name
> etc.
> How i can implement this.
> Now he is saying that if he select only one parameters in country and
> leave others,
> then report should be generated for all the coustemers for that
> country.
> Please help me...
> Regards
> Dinesh|||On Mar 30, 5:12 am, "Alphonse" <amphysv...@.gmail.com> wrote:
> Check this thread:http://groups.google.com/group/microsoft.public.sqlserver.reportingsv...
> Hope it helps.
> On Mar 30, 1:44 pm, "Dinesh" <dinesh...@.gmail.com> wrote:
> > Hi Experts,
> > I am working on Reporting services 2005 and i am new for this
> > software.
> > I have some reports, and some reports contains more than one
> > parameters.
> > Now my problem starts...
> > My client want reports with more and more parameters, and
> > at the same time he does not want to select all of them
> > He is saying that he may or may not select all the parameters.
> > and if not select all the parameters then still reports should be
> > generated.
> > for his selected parameters only.
> > Suppose i have one report with 4 parameters and first parameter is
> > Country
> > second one is states, third one is city, fourth one coustmers name
> > etc.
> > How i can implement this.
> > Now he is saying that if he select only one parameters in country and
> > leave others,
> > then report should be generated for all the coustemers for that
> > country.
> > Please help me...
> > Regards
> > Dinesh
Also, I would suggest setting up the report parameters w/a default
value of 'none selected' as part of the dataset that populates the
parameters (most likely w/a union statement in the dataset query).
That way the user can select only what is wanted and the other
parameters automatically use 'none selected.' Then design the stored
procedure/query that populates the report to handle the 'none
selected' accordingly. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant