Home » SQL & PL/SQL » SQL & PL/SQL » Shifting Data (Oracle 9.1)
Shifting Data [message #364586] Tue, 09 December 2008 03:49 Go to next message
Deon Smit
Messages: 36
Registered: September 2008
Member
My data is as follows. * count as spaces and can't use replace cuase some titles has spaces in the middle. I need to do the following.

My data


*****12
***1*AB
**TEAM1
12DREAM
*****95
THE*BIG


I want to left align the records to display as follow.


12
1*AB
TEAM1
12DREAM
95
THE*BIG

Thank you

Regards

Re: Shifting Data [message #364587 is a reply to message #364586] Tue, 09 December 2008 03:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
use LTRIM or TRIM
Re: Shifting Data [message #364645 is a reply to message #364586] Tue, 09 December 2008 07:26 Go to previous messageGo to next message
knicely87
Messages: 25
Registered: December 2008
Location: Pittsburgh, PA
Junior Member
JRowbottom is right...

The neat thing about LTRIM and RTRIM is you can trim any character from the left or right of a string...

SQL> SELECT LTRIM('*****12', '*') FROM dual;

LT
--
12

SQL> SELECT LTRIM('***1*AB', '*') FROM dual;

LTRI
----
1*AB

SQL> SELECT LTRIM('**TEAM1', '*') FROM dual;

LTRIM
-----
TEAM1

SQL> SELECT LTRIM('12DREAM', '*') FROM dual;

LTRIM('
-------
12DREAM

SQL> SELECT LTRIM('*****95', '*') FROM dual;

LT
--
95

SQL> SELECT LTRIM('THE*BIG', '*') FROM dual;

LTRIM('
-------
THE*BIG

I like the TRIM() function too. This allows you to trim a character from both the right and left side of a string at the same time!

SQL> SELECT TRIM(BOTH '*' FROM '**This is cool**') FROM dual;

TRIM(BOTH'*'
------------
This is cool

Thanks,
Jim
Re: Shifting Data [message #364660 is a reply to message #364645] Tue, 09 December 2008 08:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't put solution only hint or clue as requested in OraFAQ Forum Guide, "Responding to Posts" section:
Quote:
When responding to questions, if it is obviously a student with a homework assignment or someone else just learning, especially in the homework and newbies forums, it is usual to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, and not provide complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation.


Regards
Michel
Re: Shifting Data [message #364661 is a reply to message #364586] Tue, 09 December 2008 08:10 Go to previous messageGo to next message
knicely87
Messages: 25
Registered: December 2008
Location: Pittsburgh, PA
Junior Member
I disagree. I believe that People need to learn by example. If someone is here asking about an assignment and they learn nothing - that's fine. I've learned I can't control another's person morality. However, I will abide by the forum rules, and simply point people in the right direction.
Re: Shifting Data [message #364664 is a reply to message #364661] Tue, 09 December 2008 08:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I disagree.

You may disagree but it is the rule.
If you don't agree post it in Feedback forum but don't break the rules in your post.

Quote:
I believe that People need to learn by example.

They also need to work. Functions have been given, documentation is online, examples are in documentation, you don't need to post one and poster will learn more reading the documentation than just your example. Or are you saying that your examples are exhaustive of ALL what can be done with these functions? If not then you restrict what poster reads.

Regards
Michel
Re: Shifting Data [message #364671 is a reply to message #364664] Tue, 09 December 2008 08:36 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It's more of a guideline than a rule.

Additionally, I've learned a lot here from writing solutions to other peoples problems - there's nothing to beat actually writing code to teach you, and I'd rather it was me learning than someone else.
Re: Shifting Data [message #364673 is a reply to message #364586] Tue, 09 December 2008 08:44 Go to previous message
knicely87
Messages: 25
Registered: December 2008
Location: Pittsburgh, PA
Junior Member
I agree JRowbottom.

If someone wanted direction, they'd use google. However, if they are interested in a solution, they come to a dedicated "friendly" site.

Anyway, hopefully Deon Smit has been helped.
Previous Topic: update time
Next Topic: Issue on Date Field
Goto Forum:
  


Current Time: Fri Dec 09 08:02:57 CST 2016

Total time taken to generate the page: 0.11195 seconds