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 |
What does stand for in sql?
Is delete faster than truncate?
What is mutating error?
How is a process of pl/sql compiled?
what is the difference between union and union all
14 Answers IBM, Luxoft, Oracle,
Describe the Index, Types of index, At what situation we have used? Which one s better than others?
does sql support programming? : Sql dba
How do I access sql anywhere database?
Can I call a procedure inside a function?
how to delete duplicate rows from a join tables(I have three tables on that join) how do you know which join is need to be used? The select statement I have is: SELECT gc_skill_type.skill_type, gc_area_tec.area, gc_technology.technology, gc_technology.id_technology, gc_area_tec.id_area_tec FROM gc_skill_type, gc_area_tec, gc_technology WHERE gc_area_tec.id_skill_type (+) = gc_skill_type.id_skill_type AND gc_technology.id_area_tec (+) = gc_area_tec.id_area_tec order by gc_skill_type.skill_type asc, gc_area_tec.area asc, gc_technology.technology asc
how will be date change into string
Does sql support programming?