SQL & PL/SQL :: How To Find Extended ASCII Characters From A String

Sep 19, 2013

I have a table like mentioned below

create table test1( test_no number, test_description varchar2(100));
insert into test1 values (1,'ABC£¥');
insert into test1 values (2,'BCD£¥');

Now I am selecting from the above table and the expected rest it should have shown is as shown below

TEST1
-----------------------
TEST_NO TEST_DESCRIPTION
1 ABC£¥
2 BCD£¥

But instead of showing the extended ascii characters, it is showing some different characters as shown below

select * from test1;

TEST1
-----------------------
TEST_NO TEST_DESCRIPTION
1 ABCLY
2 BCDLY

I have an requirement where i need to fetch the exact ascii characters for £ and ¥ instead of L and Y respectively.

View 1 Replies


ADVERTISEMENT

SQL & PL/SQL :: Extended Ascii To Character

Jan 27, 2011

I have requirement to convert the extended ascii to character. Is there any function available .

View 9 Replies View Related

SQL & PL/SQL :: Delete Non-ASCII Characters?

Sep 9, 2013

In field APPLICATIONNAME are non ASCII characters. Howto delete them?for example

old value:
René

new value:
Ren

table
CREATE TABLE MIDDLEWARE_CONT
(
APPLICATIONNAME VARCHAR2(120 BYTE),
PRODUCTNAME VARCHAR2(70 BYTE),
HOSTNAME VARCHAR2(60 BYTE),

[code]....

View 14 Replies View Related

Inserting Character From Extended ASCII Character Set?

Mar 8, 2007

I'm trying to insert a character from the extended ascii character set. Specifically, there's a company that has an accented e (�) in the name. Right now, the company name doesn't have the e at all, accent or no accent. So I'm trying to do an update, something like

update table1 set company_name='blah�" where company='blah'

It runs, but doesn't do the update. Even when I try to forcefully do an insert (instead of an update) I get nowhere; the accented is simply dropped. So the basic question is, how do you insert extended ascii characters into oracle?

View 3 Replies View Related

Server Utilities :: SQL Loader Is Not Uploading Non-ASCII Characters?

Jan 10, 2012

I am using SQL Loader to load data from text file to DB. non-ASCII characters present in the text file is not uploaded correctly to DB.

Sample Data

test data üindex

Data in DB

test data ?index

View 1 Replies View Related

SQL & PL/SQL :: How To Convert String To Ascii

May 20, 2011

I can't think of a clue how to get the desired results.

I have table column with varchar2 type. I need to convert the strings in this column to ascii values. Now I know Oracle function ascii() converts a character to ascii values. But it just converts the first character of the string and ignore the others. I need to convert the whole string to ascii values. For example, if I need to convert the string "USA", the result should be '858365' (as in U decimal value is 85, S decimal value is 83 and A decimal is 65).

I think I need to use instr() function and also PL/SQL to pass all these strings through a cursor and then use a loop but I'm still confused about it.

View 5 Replies View Related

SQL & PL/SQL :: How To Get Last 4 Characters In A String

Dec 12, 2010

how to get last 4 characters in a string. But i don't know the length , for example the string is

abcdefghij

i want only ghij.

View 5 Replies View Related

SQL & PL/SQL :: Replace Characters In A String?

Apr 7, 2011

replace the first 5 commas with the character '|' in the below string:

'Red, White, Blue, Purple, Pink, Green, Yellow, Gold and many others, like Black and Silver'

I tried:

SELECT regexp_replace('Red, White, Blue, Purple, Pink, Green, Yellow, Gold and many others, like Black and Silver',
',','|',1,5) from dual

but it only replaces the 5th comma.

View 3 Replies View Related

How To Count Distinct Characters From A String

Sep 26, 2005

how to count different characters from a string ....

View 7 Replies View Related

SQL & PL/SQL :: Removal Of Special Characters In A String

Jan 2, 2013

I am looking for a SQL query which checks for special character(s) in a string and remove them if any.

for e.g: input string : 'ABCD $ & * # 123 Test'
expected output should be in upper case : 'ABCD123TEST'

we do not know the position of special characters and also what kind of special characters will come as input.

View 17 Replies View Related

SQL & PL/SQL :: Count Repeated Characters In String

Mar 9, 2011

Is there any function to count number of specific character inside string. For example :

my string is 'ABCEF AYZA'

I want to calculate how many 'A' exist in my string. result should be = 3

View 17 Replies View Related

SQL & PL/SQL :: How To Find Whether Exact String Is Present / Not In Given String

Mar 14, 2013

I'm facing some problem even after using INSTR function in Oracle.The problem is I have written the logic in the PL/SQL block which appends all the values fetched in a loop on the basis of whether the string is present or not.

For ex:

The first value fetched from the select query first is ABCDEFG which gets appended to a variable
The next value fetched is AB even this has to be appended to the variable since this exactly doesn't match with ABCDEFG.
The next value fetched is BCDEF even this has to be appended to the variable since this exactly doesn't match with ABCDEFG.
The third Value fetched is ABCDEFG this will not get appended presently according to the logic which is correct.

writing that piece of code to append the value fetched which doesn't exactly match with the existing string

View 3 Replies View Related

SQL & PL/SQL :: Remove Special Characters From Input String?

Aug 5, 2010

I have a following table,

create table test1(col1 varchar2(20));

insert into test1 values('4711-3/01');

I believe we need to use Translate function to get rid of special characters, But I would not be knowing what sort of special charecters which appear in the string, In that case how do I use Translate?

View 29 Replies View Related

SQL & PL/SQL :: Query To Get Whether A String Contains 2 Upper Characters And Numbers Using Regular_exp

Jun 12, 2013

A sample query to check whether a string contains 2 upper characters and numbers are present in that character using regular_exp .

View 12 Replies View Related

PL/SQL :: Translate Function Remove Alpha Characters From String

Feb 7, 2013

I am on 11g.

I need to remove the alpha characters from a string, leaving only numbers, but I am getting unexpected results:

SQL> SELECT TRANSLATE('3N', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', NULL) a FROM DUAL;
A
-

I thought this would leave the 3 from the 3N, but it is returning an empty string. For my application, the string '3N' could be any length, will only contain letters and numbers, and the letters will always come at the end, but there could be more than one letter

VALID INPUT samples:
4
25
11F
361NG
8ABC

View 6 Replies View Related

SQL & PL/SQL :: How To Extract First 3 Characters From Each Word In A String / Sentence And Separate With Underscore

Oct 22, 2013

I have some strings like

'Net Amount Payable by an Individual',
'Net Amount Payable by an Individual+Tax',
'Total Amount Payable towards Service',
'Total Amount Payable towards Service.+Tax'

I need to extract the first three letters from each word and separate them using an underscore. The output should be as follows for the above strings -

Net_Amo_Pay_by_an_Ind
Net_Amo_Pay_by_an_Ind_Tax
Tot_Amo_Pay_tow_Ser
Tot_Amo_Pay_tow_Ser_Tax

I request to let me know the way to get the ouput as mentioned.

View 4 Replies View Related

PL/SQL :: Employee Table - Find Characters

Sep 24, 2013

In the Employee table i want to display having 4 characters of Emplo_name ,that name's 3rd character must be 'R' How to get it .

View 9 Replies View Related

PL/SQL :: How To Find Special Characters In A Single Query

Sep 12, 2012

My query is "How to find all special characters like (%$*&@,;'/+- etc. in a single query?"

e.g.

A_MIR
A%SIM
A*SIM
A)SIM

View 6 Replies View Related

SQL & PL/SQL :: Extracting Numbers And Few Portion Of Text From A String Containing Dates / Characters And Numbers

Jul 13, 2011

I have a table test with column containing dates, characters and numbers. I have to extract the number part and the three characters before the number . My data looks like :

TEST
ID DATA
1 3/12/2007
2 0
3 3/8/2010 ABC 217
4 NONE
5 COLM XYZ 469 6/8/2011
6 LMN 209

My expected results should look like :

ID DATA
1
2
3 ABC 217
4
5 XYZ 469
6 LMN 209

View 7 Replies View Related

SQL & PL/SQL :: Find A Match Within String And Compare?

Aug 2, 2012

Below is a column 'ADDR' with the data (single column)

ADDR
--------
/shared/Folder_1 :^BIAdministrator:^BIAuthor:^BIConsumer:BISystemUser:OracleSystemUser:System:weblogic: :F

[Code]....

View 2 Replies View Related

SQL & PL/SQL :: How To Manipulate String (Find And Replace)

Jun 29, 2012

I am required to manipulate a string in this way.For example ABCDEF)* to $ABCDEF). So I should find * and delete it and instead put $ in the beginning of string.

View 9 Replies View Related

SQL & PL/SQL :: How To Find Greatest Values In A String

Jul 22, 2011

I am trying to update the greatest value in a column from a string of other column.

Ex: f the value is shown 10M+16M+25M-DG, then populate 25 only

so for that I had written query as follows:

update ANCHOR set IEL_STRAND_SIZE= greatest(
substr
(REPLACE(REPLACE(REGEXP_REPLACE( F_TYP, '[A-Z]', '' ),'+',','),'-',','),
0,
length(REPLACE(REPLACE(REGEXP_REPLACE( F_TYP, '[A-Z]', '' ),'+',','),'-',','))-1))

The output is given as 10,16,25,but not as 25.so how could i write it?Do I need to implement procedure or arrays for it.

View 7 Replies View Related

SQL & PL/SQL :: Find Occurrences Of Some String In DB Objects?

Jul 11, 2012

How can i identify all the occurences of raise_application_error(-20XXX, '<the message>'); within all database objects, and replace them with other text?

View 4 Replies View Related

SQL & PL/SQL :: Find A Negative Number In String?

Apr 24, 2012

I am currently working on a Data Dictionary project where we need to run a few rules against the give data sources to see if they all comply together.

One of the rule is to check if the no. is negative or not. So for that what I tried to do was to check if first the field is number or not and then check on if it is negative or not.

This is the code I am currently trying on and is not looking good.

SELECT 1 FROM DUAL
WHERE decode(DECODE( TRANSLATE('-123.45','-0.123456789',''), NULL, 1,0), 1,substr('-123.45',1,1) ,' ' ) = '-'

View 13 Replies View Related

SQL & PL/SQL :: Way To Find Out Ending Digits In A String?

Dec 5, 2012

Is there a way to find out the ending digits in a string?

Examples of input and outputs:
'ABC123' -> 123
'ABC' -> NULL
'123ABC' -> NULL
'123ABC456' -> 456
'123ABC456QP98' -> 98

I have figured a way to do it by doing Reverse and re-reversing using: reverse(regexp_substr(reverse(p_string), '[ [:digit:]]*'))

But I'm sure there is a way to do it backwards without using reverse function, which I am not able to properly put in the syntax.

View 3 Replies View Related

SQL & PL/SQL :: How To Find Out Lowercase Char In A String

May 16, 2008

How to find out lowercase char in a String in a SQL query

for example

"ORAclE" here c and l are lowercase so how can i find out this condition... is there any build in function in oracle?

View 13 Replies View Related

SQL & PL/SQL :: How To Find Any Character Is Repeating In A String

Sep 8, 2011

How to find whether any character in a string is repeating or not

Eg: '123LH563' should return 'YES' , as 3 is repating there
'1234567' sould return 'NO' as there is no character which is repeating

View 8 Replies View Related

PL/SQL :: Find And Replace Value In Delimited String

Sep 4, 2013

I have a requirement, where i need to find and replace values in delimited string. For example, the string is

"GL~1001~157747~FEB-13~ CREDIT~ A~N~ USD~ NULL~".

The 4th column gives month and year. I need to replace it with previous month name. For example:

"GL~1001~ 157747~ JAN-13~ CREDIT~ A~N~USD~NULL~".

I need to do same for last 12 months. I thought of first devide the values and store it in variable and then after replacing it with required value, join it back. I just wanted to know if there is any better way to do it?

View 10 Replies View Related

SQL & PL/SQL :: Find Packaged Procedures Names For Particular String

Nov 7, 2011

I want to search for some specific string in packaged procedures source code, and want to list the name of those procedures along with package names.

View 17 Replies View Related

SQL & PL/SQL :: How To Find Nearest / Closest String For A Column Data

Jun 1, 2011

Quote: I have a table(table name is names) with column as name(varchar) . I have the following data for name column.

Miss
Mississ
Mississipp

I would like to find a nearest match for Mississippi, that means sql should return row that contains Mississipp( Row #3)

If I try to find nearest match for Mississirr then sql should return row that has column value Mississ (Row#2)
Is this possible ? Here is the code for table creation and data.

create table names (name varchar2(20));

insert into names values('Miss');
insert into names values('Mississ');
insert into names values('Mississipp');
commit;

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved