Home » SQL & PL/SQL » SQL & PL/SQL » LEFT & Right functions to carve out data from a field
LEFT & Right functions to carve out data from a field [message #641208] Tue, 11 August 2015 21:59 Go to next message
Johnseito
Messages: 5
Registered: August 2015
Location: NYC
Junior Member
using a left and right function to carve out data from a field

Could someone show me an example ?

Thanks !!!
Re: LEFT & Right functions to carve out data from a field [message #641209 is a reply to message #641208] Tue, 11 August 2015 22:33 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read


>using a left and right function to carve out data from a field
I have NEVER heard of these functions.
post URL where they are documented or at least discussed.
Re: LEFT & Right functions to carve out data from a field [message #641210 is a reply to message #641209] Tue, 11 August 2015 22:52 Go to previous messageGo to next message
Johnseito
Messages: 5
Registered: August 2015
Location: NYC
Junior Member
For example





WHERE left (Column, 7) = "abcdefg"

[Updated on: Tue, 11 August 2015 22:52]

Report message to a moderator

Re: LEFT & Right functions to carve out data from a field [message #641213 is a reply to message #641210] Tue, 11 August 2015 23:23 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
AFAIK, neither LEFT() nor RIGHT() are part of the normal Oracle release.
Please provide URL where they are documented.
Re: LEFT & Right functions to carve out data from a field [message #641214 is a reply to message #641213] Tue, 11 August 2015 23:33 Go to previous messageGo to next message
Johnseito
Messages: 5
Registered: August 2015
Location: NYC
Junior Member
so no left or right functions in ORACLE ?
Re: LEFT & Right functions to carve out data from a field [message #641215 is a reply to message #641214] Tue, 11 August 2015 23:34 Go to previous messageGo to next message
Johnseito
Messages: 5
Registered: August 2015
Location: NYC
Junior Member
what can we use in place of it then ?
Re: LEFT & Right functions to carve out data from a field [message #641216 is a reply to message #641214] Tue, 11 August 2015 23:37 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Johnseito wrote on Tue, 11 August 2015 21:33
so no left or right functions in ORACLE ?


write your own functions that do what you desire.
Re: LEFT & Right functions to carve out data from a field [message #641217 is a reply to message #641216] Tue, 11 August 2015 23:39 Go to previous messageGo to next message
Johnseito
Messages: 5
Registered: August 2015
Location: NYC
Junior Member
could you give me an example of a function that will take the left 8 characters from a field ?

I am new to Oracle. Thanks
Re: LEFT & Right functions to carve out data from a field [message #641220 is a reply to message #641217] Wed, 12 August 2015 00:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SUBSTR

Database SQL Reference

Re: LEFT & Right functions to carve out data from a field [message #641276 is a reply to message #641215] Wed, 12 August 2015 16:36 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Johnseito wrote on Tue, 11 August 2015 23:34
what can we use in place of it then ?


Here's what I do when I have to ask myself such a question ..

Go the the SQL Language Reference at http://docs.oracle.com/cd/E11882_01/server.112/e41084/toc.htm
Look down the table of contents and locate the chapter on Functions. Click on that link.
Look down the list of function names for something that looks like it might be relevant.

Aside from that, the very fact that you are needing to do something like extracting some subset of a string held in a column is a very strong indication that the data model itself is flawed. Not 100% but very likely. If some subset of a string has meaning, then that substring should have been identified as a data element itself and placed in its own column. Google 'Data Normalization', 'First Normal Form', 'Second Normal Form', and 'Third Normal Form'. Wikipedia has some very good articles on the subject. And no, this is not something esoteric. It is the very heart of relational database design.
Re: LEFT & Right functions to carve out data from a field [message #641288 is a reply to message #641276] Thu, 13 August 2015 00:28 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Until recently, we (here, in Croatia) had a "number" which uniquely identified every person. It was called "JMBG" (something like the Social Security Number in the USA, unless I'm wrong). It consisted of 13 digits which represented certain information. I can't remember exactly what all of them were (don't feel like investigating now), but these were date of birth, gender, republic you were born in (that term goes back to former Yugoslavia which consisted of 6 republics), etc.

Therefore, if you needed someone's birthday, you just extracted it from the JMBG using SUBSTR. Or his/her gender. Or some other information.

I'm not saying that that information shouldn't be / wasn't stored in a separate column in databases spread throughout the country, but you certainly had a way to find that information using JMBG and I doubt that normalizing JMBG, i.e. having it split into several pieces of information and stored into separate columns and - therefore - losing JMBG itself as a whole - would be a good idea.

So, from my point of view, OP's data model might be flawed, or not. Can't tell due to lack of information.
Re: LEFT & Right functions to carve out data from a field [message #641290 is a reply to message #641276] Thu, 13 August 2015 00:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Google 'Data Normalization', 'First Normal Form', 'Second Normal Form', and 'Third Normal Form'.


And our Normalization wiki page for a detail example of these forms.

Re: LEFT & Right functions to carve out data from a field [message #641295 is a reply to message #641290] Thu, 13 August 2015 02:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Until recently, we (here, in Croatia) had a "number" which uniquely identified every person. It was called "JMBG" (something like the Social Security Number in the USA, unless I'm wrong).


We have the same thing in France, it is (for the general case, French born in the current metropolitan France territory):
GYYMMDDTTTNNNCC
| | | | |  |  |-> 2 digits, control code (97 minus the remainder of integer division of the first 13 digits by 97)
| | | | |  |----> 3 digits, number of your birth in the town for the month you were born 
| | | | |                   (hoping there are not more than 999 births in the month, big city are divided 
| | | | |                    in "arrondissement" (district), 20 for Paris, each one with its number)
| | | | |-------> 3 digits, number of the town in the "département" (see below)
| | | |---------> 2 digits, "département" number (metropolitan France is divided in 96 administrative "départements")
| | |-----------> 2 digits, month of birth
| |-------------> 2 digits(!), year of birth
|---------------> 1 digit, gender 1: male, 2: female 
But as there are many exceptions (as always in France, and exceptions on exceptions...), you can't rely on this number to determine the birth date (since there is not the day) or even the gender.

[Updated on: Thu, 13 August 2015 02:55]

Report message to a moderator

Re: LEFT & Right functions to carve out data from a field [message #641300 is a reply to message #641295] Thu, 13 August 2015 02:46 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
In the case of something like a JMBG I'd store the whole thing then add virtual columns for the individual bits
Re: LEFT & Right functions to carve out data from a field [message #641330 is a reply to message #641288] Thu, 13 August 2015 07:23 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Littlefoot wrote on Thu, 13 August 2015 00:28
Until recently, we (here, in Croatia) had a "number" which uniquely identified every person. It was called "JMBG" (something like the Social Security Number in the USA, unless I'm wrong). It consisted of 13 digits which represented certain information. I can't remember exactly what all of them were (don't feel like investigating now), but these were date of birth, gender, republic you were born in (that term goes back to former Yugoslavia which consisted of 6 republics), etc.
...
So, from my point of view, OP's data model might be flawed, or not. Can't tell due to lack of information.


Which is why I said "Not 100% but very likely."
In auto manufacturing you have the Vehicle Identity Number(VIN). It is most definately a data element in itself, but but various parts do have various meanings. As I recall (it's been 10 years since I worked for an auto manufacturer) the VIN is 15 characters, and the last 6 is the numeric serial number of the car. Various parts of the first 9 characters indicate the company, the assembly plant, the model or vehicle type, etc.

So yes there are 'exceptions', but it is certainly a 'red flag' to be re-thought and pointed out to newbies. And often, not-so-newbies.
Re: LEFT & Right functions to carve out data from a field [message #641437 is a reply to message #641330] Fri, 14 August 2015 15:17 Go to previous message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
You use substr in place of the tsql commands left and right

to get the first 3 character in a string (left) substr(string,1,3)
to get the last 3 characters in a string (right) substr(string,-3)
Previous Topic: rownum and order by
Next Topic: Need a Help on SQL query
Goto Forum:
  


Current Time: Thu Mar 28 20:27:29 CDT 2024