Like shown below i have 3 columns(Name,No,Address). The
values in name column i want to modity.
Requirement : Keep only once space between two words
(Fname,Lname) in the Name column.
For this what is the query? Please answer me. Advance
Thanks.
Name No Address Reference
manoj kumar
kumar raja
vinzay kumar
rajendra prasad
gowri nath -- -- --
Answers were Sorted based on User's Feedback
Answer / kavitha n
SELECT NAME,
( SUBSTR (NAME, 1, (INSTR (NAME, ' ', 1)))
|| ''
|| SUBSTR (NAME, INSTR (NAME, ' ', -1), LENGTH (NAME))
) output
FROM test;
Is This Answer Correct ? | 5 Yes | 2 No |
Answer / lince
select substring(NAME,1,charindex(' ',NAME))+' '+
replace(substring(NAME,charindex(' ',NAME),len(NAME)),' ','')
as NAME from test
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / kalyan
update test set NAME=regexp_replace(NAME,'( ){2,}', ' ')
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / manojkumar
Thanks to Kavitha and Lince.
Both answers are correct in oracle and in Sql Server.
Excellent
Thanks a lot
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / swastik
select substr(name,1,instr(name,' ',1)-1)||substr(name,instr(name,' ',-1),length(name)) from spacename
Is This Answer Correct ? | 0 Yes | 0 No |
how will you find out the last three records in a table with n no of records and delete them
what are the advantages of sql ? : Sql dba
What is the purpose of design view?
What is the usage of nvl function?
What does count (*) do in sql?
What is cold data?
How can I get the number of records affected by a stored procedure?
what is the difference between char_length and length? : Sql dba
Why stored procedure is faster than query?
IN A TABLE HAVE ONE COLUMN PRIMARY KEY..IT WILL NOT ALLOWS NULL VALUES AND DUPLICATE VALUES..INSTEAD OF PRIMARY KEY WHY CANT WE USE UNIQUE AND NOT NULL.THESE TWO ALSO DOESNT ACCEPT NULL VALUES IN NOT NULL AND UNIQUE DOESNT ACCEPT DUPLICATE VALUES? SO WHAT IS THE DIFEERENCE BETWEEN(UNIQUE,NOT NULL) AND PRIMARY KEY??????
What is sqlite used for?
How to get unique records from a table?