Login or Register to Ask a Question and Join Our Community


Programming

SQL Issue please respond !!!

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Programming SQL Issue please respond !!!
# 1  
Old 04-03-2009
SQL Issue please respond !!!
Hi Folks,

I have a issue regards space in the records.

Example:

999207404 |sp818056|STEVEN |LAWRENCE |Mike.S.Lawrence@sprint.com |2003 S Data Sales Mgr 77 |SVC |1000000034|SVC-EN-SOL SUPPORT |2000120016|SVC-SOL ENG-WEST |2000113271|SVC-SOL SUP-VAN DEN |2000122863|0


Is the single row I need to remove the extrax spacese from entier records.

Expected o/p is:
999207404|sp818056|STEVEN|LAWRENCE|Mike.S.Lawrence@sprint.com |2003 S Data Sales Mgr 77|SVC|1000000034|SVC-EN-SOL SUPPORT |2000120016|SVC-SOL ENG-WEST|2000113271|SVC-SOL SUP-VAN DEN|2000122863|0



Please suggest your answers as soon as possible!!!

Rgds
Ann
# 2  
Old 04-03-2009
And also If there is any balnk cell I need to remove that as well please suggest.
# 3  
Old 04-03-2009
Do you want to remove the spcaes in the out file, or change sql code to remove the spaces?
If it is a sql change, we need to see the sql code.
# 4  
Old 04-03-2009
Sanchay
In sql you can use the inbuilt function REPLACE(orig_string, string_to_be_replaced, new_String)

For e.g.
SELECT ('abcd efg hij',' ',null) from dual;
OR
SELECT ('abcd efg hij',' ') from dual;

will yield

abcdefghij
# 5  
Old 04-03-2009
but how do i explicitly give values 'abcd efg hij' I have around 200000 records for that table and I need to remove all the blank spaces!
# 6  
Old 04-03-2009
You'll have to write this for every column of the table.

Just update the table columns like :

update <table name>
set column1 = REPLACE(column1, ' '),
column2 = REPLACE(column2, ' '),
.
.
.
<where clause if any>

--check before commit
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

awk concatenation issue - SQL generation

Greetings Experts, I have an excel file and I am unable to read it directly into awk (contains , " etc); So, I cleansed and copied the data into notepad. I need to generate a script that generates the SQL. Requirement: 1. Filter and select only the data that has the "mapping" as "direct"... (4 Replies)
Discussion started by: chill3chee
4 Replies

2. Shell Programming and Scripting

Issue with quotes when running SQL command from within su -c

RHEL 6.2/Bash shell root user will be executing the below script. It switches to oracle user logs in using sqlplus and tries to run the below UPDATE statement. All the commands after su -c are enclosed in a single quote delimited by semicolon. The execution has failed because the quotes... (3 Replies)
Discussion started by: omega3
3 Replies

3. Shell Programming and Scripting

Issue with SQL UNIX shell script -

Hi all I am writing a shell script which will run a select query from a table . If the ouput is not 500 - then send a email to the team saying there is a error . But i am not sure how to redirect this output of the select query to the log file - #!/bin/ksh sqlplus /nolog conect... (1 Reply)
Discussion started by: honey26
1 Replies

4. Shell Programming and Scripting

Sql issue in shell scripting

HI friends , i am also facing an issue in mysql i ma trying to insert detail in a variable but not got success #!/bin/sh mysql -u<username> -p<password> <dbname> << EOF DEV=`mysql --skip-column-names <dbname> -e "SELECT timestamp from process_record where id = 1"` EOF echo $DEV ERROR... (3 Replies)
Discussion started by: sanjay833i
3 Replies

5. Shell Programming and Scripting

Issue in SQL Loader scripts

Hi, I'm planning to load the data from FLAT files into tables. Source file: more input.txt LRNO|Bale|Horsepower|NumberOfBarges|BollardPull|NumberOfCars|GasCapacity|GrainCapacity|IndicatedHorsepower|LiquidCapacity|... (6 Replies)
Discussion started by: shyamu544
6 Replies

6. Programming

SQL Developer JOINS / GROUP BY issue.

Am having a nightmare with a certain piece of code.. have tried almost everything and just cannot see what the issue is.. CREATE OR REPLACE VIEW TOP_EARNER_PER_LOCATION AS SELECT E.FIRST_NAME || ' ' || E.LAST_NAME AS EMPLOYEE_NAME, L.REGIONAL_GROUP AS REGIONAL_GROUP, ... (1 Reply)
Discussion started by: U_C_Dispatj
1 Replies

7. Programming

SQL IF-THEN-ELSE issue

Hi All, I have a table with the following columns: sysName, date, time, cpuNum, cpuPercentageBsy There are multiple system names and multiple CPU numbers. I need to produce a report that shows the cpuPercentageBsy for cpuNum's 0 and 1 and then an average of cpuPercentageBsy for all the... (1 Reply)
Discussion started by: pondlife
1 Replies

8. Shell Programming and Scripting

KSH script SQL timeout issue

Hi all, I have a KSH script which is kicking off an sql scripts as follows: /usr/local/installs/instantclient_10_2/sqlplus -s username/password @$sql_path/sql_query.sql > $tmp_path/sql_query_results The problem I have is that sometimes the 10g Oracle Database spits out an error saying... (4 Replies)
Discussion started by: Donkey25
4 Replies

9. Shell Programming and Scripting

SQL scripts not running, possible timeout issue?

I am a novice Unix scripter and need a little advice/help on a script I've written that's causing some problems. We are using Solaris 9 on a Sun box and the script is invoked with the korn shell. I have a two-part question: I wrote a shell script that calls and executes 3 separate sql scripts,... (3 Replies)
Discussion started by: E2004
3 Replies
Login or Register to Ask a Question