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 |
is it possible to pass an object or table to a procedure as an argument?
Does varchar need length?
How do I count rows in sql?
what are integrity rules?
Is sqlite good enough for production?
What are the basic sql commands?
Is sql database free?
what is mean by forward declaration and where we'll use it.
Why do we need sharding?
table :- city name country code abc 11 bcd 22 cde 232 def 33 write a procedure , so that when we give a phone no. eg - 1123456789 - o/p sud be city name = abc if phone no. - 2322345897 , o/p sud be =cde note - bcd and cde city name sud be diff. as dey diff only with th last no. Pls ans. this questnion.
what is the difference between ereg_replace() and eregi_replace()? : Sql dba
What is AUTH_ID and AUTH_USER in pl/sql ?