Home » SQL & PL/SQL » SQL & PL/SQL » how to treat char(30) as space-delimited number?
how to treat char(30) as space-delimited number? [message #210216] Tue, 19 December 2006 20:08 Go to next message
tpierce
Messages: 7
Registered: December 2006
Location: San Francisco
Junior Member
our part #s were once numbers, but our new Oracle package wants char(30) so there we are, no leading zeroes, space-delimited. need to select all less-than-5-digit numeric part#s. something like
select to_number(item_code) as partNo from item_catalog where substring(item_code,5,1)=' ';

-- almost, but squawks & dies at 1A1 "invalid number".
-- need something like pattern matching...hoping to convey my meaning; need a working version of:

... where substring(item_code,1,5)={[0-9][0-9,' '][0-9,' '][0-9,' ']' '} and substring(item_code,5,25)=' '

-- in which I'm trying to say "digit,(digit-or-space)x3,(space)x26"

and I need them as numbers for numeric sort - the users love their long-known numerically sorted list. no db changes allowed!, this has to be sql select.

[Updated on: Tue, 19 December 2006 20:46]

Report message to a moderator

Re: how to treat char(30) as space-delimited number? [message #210283 is a reply to message #210216] Wed, 20 December 2006 02:57 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What version of Oracle are you on?
If you're on 10G, then the REGEX expression is probably exactly what you're looking for.

If not....
1) Can you create functions on the Db, or does that count as Db changes
2) Apart from spaces in the fields, is the data in this column all numeric, or do you have non-numeric chrs in there as well
Re: how to treat char(30) as space-delimited number? [message #210372 is a reply to message #210216] Wed, 20 December 2006 09:02 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
How about something like

select *
from my_table
order by lpad(ltrim(rtrim(formally_number_column)),30,'0')
Re: how to treat char(30) as space-delimited number? [message #210440 is a reply to message #210372] Wed, 20 December 2006 18:33 Go to previous messageGo to next message
tpierce
Messages: 7
Registered: December 2006
Location: San Francisco
Junior Member
no, I'm on 9i, so apparently no regex().
no, no function creation allowed (yet).
yes, there's non-digit data in there.

ltrim & rtrim are good pointers, THANKS!.

getting lucky, ltrim turns out to be unnecessary in this case - no leading spaces. so here's my approach, which does work:

select * from ta_item_catalog where
length(rtrim(item_code,' ')) < 5
and substr(lpad(rtrim(item_code,' '),4,'0'),1,1) <= '9'
and substr(lpad(rtrim(item_code,' '),4,'0'),1,1) >= '0'
and substr(lpad(rtrim(item_code,' '),4,'0'),2,1) <= '9'
and substr(lpad(rtrim(item_code,' '),4,'0'),2,1) >= '0'
and substr(lpad(rtrim(item_code,' '),4,'0'),3,1) <= '9'
and substr(lpad(rtrim(item_code,' '),4,'0'),3,1) >= '0'
and substr(lpad(rtrim(item_code,' '),4,'0'),4,1) <= '9'
and substr(lpad(rtrim(item_code,' '),4,'0'),4,1) >= '0'
order by lpad(rtrim(item_code,' '),4,'0')

yes it works, but gawd what a dog. is there a better way?
(functions?)

and is there a way to include my manipulated item in my results? If I could just give it a name...



Tracy Pierce
Re: how to treat char(30) as space-delimited number? [message #210443 is a reply to message #210440] Wed, 20 December 2006 18:59 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Can you provide some sample data? It may help to understand the requirements a little better.
Re: how to treat char(30) as space-delimited number? [message #210447 is a reply to message #210443] Wed, 20 December 2006 19:35 Go to previous messageGo to next message
tpierce
Messages: 7
Registered: December 2006
Location: San Francisco
Junior Member
1
11
1A1
1111
1112
2
22
23
234
2345
23A
OddNumber
34
234565
12345
245245
24352-2345-44234

There are all sorts of "numbers" in the data; the ones in which I'm interested are
o no longer than 4 characters,
o contain only digits 0-9
Re: how to treat char(30) as space-delimited number? [message #210451 is a reply to message #210447] Wed, 20 December 2006 19:54 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Are you looking for something like this?

SQL> select * from test_num;

A1
--------------------
1
11
1A1
1111
1112
2
22
23
234
2345
23A
34
234565
12345
245245
24352-2345-44234

16 rows selected.

SQL> select a1 from test_num
  2     where trim(translate(a1,' 1234567890',' ')) is null
  3     and length(a1) <= 4;

A1
--------------------
1
11
1111
1112
2
22
23
234
2345
34

10 rows selected.
Re: how to treat char(30) as space-delimited number? [message #210649 is a reply to message #210451] Thu, 21 December 2006 12:09 Go to previous messageGo to next message
tpierce
Messages: 7
Registered: December 2006
Location: San Francisco
Junior Member
Beautiful! I think that's EXACTLY what I want; I can't imagine a more efficient way to do it, even with lower languages. Thank you, ebrian! (and everybody else too, ;-)

(The only trouble spot I can imagine (which doesn't happen to exist in my data where length(trim(part_no))<5) is embedded blanks, as in part# "1 23")


Re: how to treat char(30) as space-delimited number? [message #210658 is a reply to message #210649] Thu, 21 December 2006 12:25 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
You're welcome.

Embedded spaces can be accommodated for too. Are you saying if the number is '123 5', then it should be returned as a "GOOD" value? If so, then you can change the code to:

SQL> select a1 from test_num
  2     where trim(translate(a1,' 1234567890',' ')) is null
  3     and length(replace(a1, ' ')) <= 4;
Re: how to treat char(30) as space-delimited number? [message #210666 is a reply to message #210216] Thu, 21 December 2006 12:46 Go to previous messageGo to next message
tpierce
Messages: 7
Registered: December 2006
Location: San Francisco
Junior Member
no, "123 5" is 5 chars, so it's out. "12 3", though, should be invalid, as the rules are
o chars 0-9
o lenght < 5
but I think it falls through the sieve as valid...

select * from ta_item_catalog
where trim(translate(item_Code,' 0123456789',' ')) is null
and length(trim(item_code)) < 5
order by item_code;

but adding a replace() (thanks again!)...

select * from ta_item_catalog
where trim(translate(item_Code,' 0123456789',' ')) is null
and length(trim(item_code)) < 5
and replace('!',trim(substr(item_code,1,4)),' ') <> '!'
order by item_code;

should disallow it. This brings up a question: Can I, mid-select give
"trim(translate(item_code,' 0123456789',' '))"
a name, or create a temp variable to hold that result, for subsequent further manipulation? it seems a shame to have to go back to item_code to check for blanks - it's already been massaged quite a bit.


another q if you don't mind: I see you're testing length(A1) directly, and wonder how you can do that. terminated by nulls? my item_code is char(30).
Re: how to treat char(30) as space-delimited number? [message #210670 is a reply to message #210666] Thu, 21 December 2006 13:00 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
tpierce wrote on Thu, 21 December 2006 13:46
Can I, mid-select give
"trim(translate(item_code,' 0123456789',' '))"
a name, or create a temp variable to hold that result, for subsequent further manipulation? it seems a shame to have to go back to item_code to check for blanks - it's already been massaged quite a bit.

another q if you don't mind: I see you're testing length(A1) directly, and wonder how you can do that. terminated by nulls? my item_code is char(30).



You are absolutely correct. I created my test table as VARCHAR2 so the length padded spaces didn't affect my query. But as you indicated a trim around the column will take care of that.

A modified version that should prevent numbers with spaces would be:

SQL> select a1 from test_num
  2	where translate(trim(a1), ' 1234567890', '-') is null
  3	and length(trim(a1)) < 5;


Not sure what type of further manipulation you mean?
Re: how to treat char(30) as space-delimited number? [message #210677 is a reply to message #210216] Thu, 21 December 2006 13:54 Go to previous messageGo to next message
tpierce
Messages: 7
Registered: December 2006
Location: San Francisco
Junior Member
translate(trim(item_code),' 0123456789','-')) is null
and length(trim(item_code)<5

validates perfectly, you rock!

on subsequent manipulation:

I'm trying to avoid repeated processes; local variables could let me do that. In this example, maybe it's just trim(), but I'd like to include a version of item_code in my output data that will let it sort to numeric sequence (in which item_code "2" precedes item_code "11", etc). I can either

lpad(trim(item_code),4,0)
or
cast (trim(item_code) as number)

to achieve the order I want, but how do I include it in my output? (this so I can sort that output on other columns and be able to return to that sequence)
if I could say
newNumber = trim(item_code)
newNumber = translate(...)
followed by
if
cast newNumber as

but I guess the question is more generic than that - can I create data elements in the course of a select?
Re: how to treat char(30) as space-delimited number? [message #210681 is a reply to message #210677] Thu, 21 December 2006 14:26 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
As you mentioned, you could obtain the sort you require by including the following order by at the end of your query:

order by to_number(a1)


DECODE may be able to provide some of the manipulation you are looking for, but I can't say for certain without more insight into the requirement. If it's complex manipulation, you may have to resort to PL/SQL. Sorry, I still may not be clear on the requirement.
Re: how to treat char(30) as space-delimited number? [message #210858 is a reply to message #210681] Fri, 22 December 2006 12:03 Go to previous message
tpierce
Messages: 7
Registered: December 2006
Location: San Francisco
Junior Member
I guess it's a hypothetical question: Can I create variables within a single SQL statement?

I know I can create reference names as in
...from owner.longtablename x where x.column ...

I'm still pretty confused about where TOra ends and PL/SQL begins - I was under the impression TOra uses PL/SQL, so maybe vars are possible. Still too green to say, so let's wrap this one up with a final

THANKS very much, this thread was a great little lesson, and I've found a great resource.

back to work,

TPierce
Previous Topic: What is problem with Check Constraint
Next Topic: to get distict values
Goto Forum:
  


Current Time: Thu Dec 08 18:46:35 CST 2016

Total time taken to generate the page: 0.11460 seconds