That was the common solution.
If you know what the unreadable characters consists your string (for example X'05', X'0D', X'14') you can use shorter statement, like:
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
Hello,
Quote:
Unreadable characters how you know from X'00' to X'3F' and X'FF'.
There are many more than these. . . .
This is neither a UNIX nor Windows (ascii) environment and the character set is not sensitive to values "below spaces". For example x'59' and x'DA' are fairly useless as screen/report values. . .
I believe the time spent trying to fix junk values in database coumns would be better spent preventing them in the first place. . .
Unreadable characters how you know from X'00' to X'3F' and X'FF'.
There are many more than these. . . .
This is neither a UNIX nor Windows (ascii) environment and the character set is not sensitive to values "below spaces". For example x'59' and x'DA' are fairly useless as screen/report values. . .
I believe the time spent trying to fix junk values in database coumns would be better spent preventing them in the first place. . .
You are right as usual.
You can add all unreadable characters known to you to this string:
X'000102...3F' where doesn't matter what order.
They'll will convert all of them to X'FF' by Translate function , then remove by Replace function.
How I shown before.
You can't prevent all mistakes, but you have to be ready to fix them.
How we can understand we have to select string with
'0123456789+-.', also in string could one and only one '-', '+' and '.'.
In practice is not so seldom problem, how I know.
Quote:
step1: Remove space from the string. String has not to be change
step2: Transate all characters in string '+-.123456' to space.
step3: Remove '-', '+', '.' from the string. For each remove length of the string has to become 1 character less
step4. Find position of '+' and '-'. Possition has to be first or last in the string
step5. '+' and '-' has not to be in the same string
select column1
from
(select strip( column1) from table1) ii
where
replace( column1, ' ', '') = column1
and
Translate(column1, ' ', '0123456789+-.') = ' '
and
length(replace( column1, '+', '')) >= length(column1) - 1
and
length(replace( column1, '-', '')) >= length(column1) - 1
and
length(replace( column1, '.', '')) >= length(column1) - 1
and
posstr(column1, '-') in (0, 1, length(column1) )
and
posstr(column1, '+') in (0, 1, length(column1) )
and not exists
(select * from sysibm.sysdummy1
where posstr(column1, '-') > 0
and
posstr(column1, '+') > 0)
I have to make small but important correction to the code:
Quote:
(select strip(column1) column1 from table1) ii
Code:
select column1
from
(select strip(column1) column1 from table1) ii
where
replace( column1, ' ', '') = column1
and
Translate(column1, ' ', '0123456789+-.') = ' '
and
length(replace( column1, '+', '')) >= length(column1) - 1
and
length(replace( column1, '-', '')) >= length(column1) - 1
and
length(replace( column1, '.', '')) >= length(column1) - 1
and
posstr(column1, '-') in (0, 1, length(column1) )
and
posstr(column1, '+') in (0, 1, length(column1) )
and not exists
(select * from sysibm.sysdummy1
where posstr(column1, '-') > 0
and
posstr(column1, '+') > 0)
which is not valid in DB2.
I corrected the query in 2nd time,
Code:
select column1
from
(select strip(column1) column1 from table1) ii
where
replace( column1, ' ', '') = column1
and
Translate(column1, ' ', '0123456789+-.') = ' '
and
length(replace( column1, '+', '')) >= length(column1) - 1
and
length(replace( column1, '-', '')) >= length(column1) - 1
and
length(replace( column1, '.', '')) >= length(column1) - 1
and
posstr(column1, '-') in (0, 1)
and
posstr(column1, '+') in (0, 1)
and not exists
(select * from sysibm.sysdummy1
where
posstr(column1, '-') > 0
and
posstr(column1, '+') > 0)
1. string has to have at least one digit
2. I want accept strings: '777.888-', '- 12345', '+ 456.98'
3. Inner table has to bring as only decimal likes stings
Code:
select column1
from
(select case when substr(strip(column1), length(strip( column1)), 1)
in ('+', '-')
then
substr(strip(column1), length(strip( column1)), 1)
||
substr(strip(column1), 1, length(strip( column1)) - 1)
when substr(strip( column1), 1, 1) in ('+', '-')
then
substr(strip( column1), 1, 1)
||
strip(substr(strip(column1), 2))
else strip( column1)
end AS column1
from table1
where Translate(column1, ' ', '0123456789+-.') = ' '
and column1 > Translate(column1, ' ', '0123456789')
) ii
where
replace( column1, ' ', '') = column1
and
length(replace( column1, '+', '')) >= length(column1) - 1
and
length(replace( column1, '-', '')) >= length(column1) - 1
and
length(replace( column1, '.', '')) >= length(column1) - 1
and
posstr(column1, '-') in (0, 1)
and
posstr(column1, '+') in (0, 1)
and not exists
(select * from sysibm.sysdummy1
where
posstr(column1, '-') > 0
and
posstr(column1, '+') > 0)
with
Source(source_str) as
(select 'You are the best DB2 programmers in the world !!!!'
from sysibm.sysdummy1
)
,
String_cleaner(result_string) as
(
select strip(source_str) from Source
union All
select replace(result_string, ' ', ' ') from String_cleaner
where Length(replace(result_string, ' ', ' ')) < length(result_string)
)
,
Final_operation(result_string, source) as ( select result_string, source_str
from String_cleaner, Source
where length(result_string)
= (select min(length(result_string)) from String_cleaner)
)
select source "Source String", result_string "Result String"
from Final_operation
Result of run will be:
Quote:
You are the best DB2 programmers in the world !!!!
with
Source(source_str) as
(select 'You are the best DB2 programmers in the world !!!! '
from sysibm.sysdummy1
)
,
string_posn(posn, charposn, iterno) as
(
select int(1), substr(source_str, 1, 1), length(source_str)
from Source
union all
select posn + 1, substr(source_str, posn + 1, 1), iterno
from string_posn, Source
where posn + 1 <= length(source_str)
)
,
Final_operation(result_string, lastchar, K) as
(select varchar(charposn, 1000), charposn, 1
from string_posn where posn = 1
union all
select result_string ||
case
when lastchar = ' ' and charposn = ' '
then ''
else charposn
end, charposn, K + 1
from Final_operation, string_posn
where posn = K + 1
and K + 1 <= iterno
)
,
Get_result_string(source_str, result_string) as
( select source_str, result_string
from Final_operation, Source
where K = (select max(iterno) from string_posn)
)
select source_str, result_string
from Get_result_string