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