by jayzhe » Fri, 11 May 2001 21:39:32
I have a column that has state abbreviations (GA). I need to convert them to
the full name. Can regular SQL do this? Not sure if I somehow have to loop
through the rows and use if/then/else logic.
Tks
jz
by Steve Lon » Fri, 11 May 2001 21:57:42
1. if you mean replace the use of two letter abbreviations by full names,
just type the names out in a sql insert command "insert into states value
('Alabama'); insert into states ('etc');
2. if you mean using the two letter code to look up the full name, i
suggest having a table with two columns, the two letter in the first column
and the full name in the second column. then use "select full_name from
states where two_letter = &abbrev"
3. a really ugly and inefficient way to do it is using DECODE....you can
look this one up if you want to.
Quote:> Hello all. I am looking for a little point in the right direction with
data
> conversions.
> I have a column that has state abbreviations (GA). I need to convert them
to
> the full name. Can regular SQL do this? Not sure if I somehow have to
loop
> through the rows and use if/then/else logic.
> Tks
> jz
by Patrick Sink » Fri, 11 May 2001 22:14:16
select decode( 'AL', 'alabama'
, 'CA', 'California'
, ...... -- and so on
, 'Unknown' -- value if none of the above match
)
from table
Also, you could write a function that looks up the code and returns the
description in a table.
This function has to be placed in a package then, and given a PRAGMA
RESTRICT REFERENCES( function_name, WNDS, WNPS )
select package.function( 'abbreviation' )
from table;
Good luck!
Regards,
Patrick Sinke
Cap Gemini Ernst & Young, sector Financial Services.
Quote:> Hello all. I am looking for a little point in the right direction with
data
> conversions.
> I have a column that has state abbreviations (GA). I need to convert them
to
> the full name. Can regular SQL do this? Not sure if I somehow have to
loop
> through the rows and use if/then/else logic.
> Tks
> jz
by jayzhe » Fri, 11 May 2001 23:03:28
select state, decode (state,'ID','Indiana','PA','Pennsylvania') from user
/
Thanks!!
>It's gonna be a large piece of code, but you can do it with Decode!
>select decode( 'AL', 'alabama'
> , 'CA', 'California'
> , ...... -- and so on
> , 'Unknown' -- value if none of the above match
> )
>from table
>Also, you could write a function that looks up the code and returns the
>description in a table.
>This function has to be placed in a package then, and given a PRAGMA
>RESTRICT REFERENCES( function_name, WNDS, WNPS )
>select package.function( 'abbreviation' )
>from table;
>Good luck!
>Regards,
>Patrick Sinke
>Cap Gemini Ernst & Young, sector Financial Services.
>> Hello all. I am looking for a little point in the right direction with
>data
>> conversions.
>> I have a column that has state abbreviations (GA). I need to convert them
>to
>> the full name. Can regular SQL do this? Not sure if I somehow have to
>loop
>> through the rows and use if/then/else logic.
>> Tks
>> jz
by Patrick Sink » Fri, 11 May 2001 23:17:32
update table
set state = decode (state,'ID','Indiana','PA','Pennsylvania')
Regards,
Patrick Sinke
Cap Gemini Ernst & Young, sector Financial Services.
> select state, decode (state,'ID','Indiana','PA','Pennsylvania') from user
> /
> Thanks!!
> >It's gonna be a large piece of code, but you can do it with Decode!
> >select decode( 'AL', 'alabama'
> > , 'CA', 'California'
> > , ...... -- and so on
> > , 'Unknown' -- value if none of the above match
> > )
> >from table
> >Also, you could write a function that looks up the code and returns the
> >description in a table.
> >This function has to be placed in a package then, and given a PRAGMA
> >RESTRICT REFERENCES( function_name, WNDS, WNPS )
> >select package.function( 'abbreviation' )
> >from table;
> >Good luck!
> >Regards,
> >Patrick Sinke
> >Cap Gemini Ernst & Young, sector Financial Services.
> >> Hello all. I am looking for a little point in the right direction with
> >data
> >> conversions.
> >> I have a column that has state abbreviations (GA). I need to convert
them
> >to
> >> the full name. Can regular SQL do this? Not sure if I somehow have to
> >loop
> >> through the rows and use if/then/else logic.
> >> Tks
> >> jz
by jz » Sat, 12 May 2001 19:55:46
tks for the help.
>That should be easy, don't you think?
>update table
>set state = decode (state,'ID','Indiana','PA','Pennsylvania')
>Regards,
>Patrick Sinke
>Cap Gemini Ernst & Young, sector Financial Services.
>> If I use this syntax i get what I want displayed, how do you actually
>write this
>> to update the column?
>> select state, decode (state,'ID','Indiana','PA','Pennsylvania') from user
>> /
>> Thanks!!
>> >It's gonna be a large piece of code, but you can do it with Decode!
>> >select decode( 'AL', 'alabama'
>> > , 'CA', 'California'
>> > , ...... -- and so on
>> > , 'Unknown' -- value if none of the above match
>> > )
>> >from table
>> >Also, you could write a function that looks up the code and returns the
>> >description in a table.
>> >This function has to be placed in a package then, and given a PRAGMA
>> >RESTRICT REFERENCES( function_name, WNDS, WNPS )
>> >select package.function( 'abbreviation' )
>> >from table;
>> >Good luck!
>> >Regards,
>> >Patrick Sinke
>> >Cap Gemini Ernst & Young, sector Financial Services.
>> >> Hello all. I am looking for a little point in the right direction with
>> >data
>> >> conversions.
>> >> I have a column that has state abbreviations (GA). I need to convert
>them
>> >to
>> >> the full name. Can regular SQL do this? Not sure if I somehow have to
>> >loop
>> >> through the rows and use if/then/else logic.
>> >> Tks
>> >> jz
by John Russel » Sun, 13 May 2001 17:17:38
>select decode( 'AL', 'alabama'
> , 'CA', 'California'
> , ...... -- and so on
> , 'Unknown' -- value if none of the above match
> )
>from table
Not that I have ever done this! :-)
John
--
Got an Oracle database question?
Try the search engine for the database docs at:
http://tahiti.oracle.com/
by Patrick Sink » Tue, 15 May 2001 17:21:52
that's a nice trick! I don't think I would have come up with that!
Though I must say that I prefer to use a stored function as described in my
posting. It is more generic and flexible, and errors are less likely to
occur.
Regards,
Patrick Sinke
Cap Gemini Ernst & Young, sector Financial Services
> >select decode( 'AL', 'alabama'
> > , 'CA', 'California'
> > , ...... -- and so on
> > , 'Unknown' -- value if none of the above match
> > )
> >from table
> If you have too many choices in DECODE, it errors out. A way around
> this is to make the final default value NULL and use NVL to call a
> second DECODE if the first one didn't have any matches. (And a third
> if you really have a lot of values.)
> Not that I have ever done this! :-)
> John
> --
> Got an Oracle database question?
> Try the search engine for the database docs at:
> http://tahiti.oracle.com/
1. Foreign character conversion when doing an import
Hi,
Help please!!!!
I've got a CSV file of Spanish text that I need to import using
Microsoft SQL Server 7. When I use the import wizard I end up with
non standard characters not being correctly converted i.e. becomes
. I've tried playing around with OEM rather ANSI File Types on the
Import but whereas the preview using OEM looks correct I'm unable to
specify a column delimiter.
I've spent most of the day either trying different methods or looking
on the web but no joy. I've found plenty of other people with the
same question but noone who has been able to answer it.
Any help would be gratefully received & if you're anywhere near Henley
in the UK I'd gladly buy you a drink :-).
Can you please also post any replies here.
Thanks in advance,
Andee
3. Pick64 to Mentor conversion.....NE1 done it?
5. conversion of ODBC SQL data types to ODBC C data types
6. SQLMail
7. Disallowed implicit conversion from data type nvarchar to data type money
8. Using Packages in Oracle forms
9. Data type conversion error when saving numeric data types
10. Data conversion problems handling DB2 Decimal data types
11. Has anyone used Data Junction data conversion tools?
12. REQ: Review of Data Junction (data conversion program)
13. Dataflex / Data Access - Data conversion