weston_086 (9/10/2012)

---

I have a table name Test_name.which has two columns full_name and nick_name.I have values only in full_name where as Nick_name column is blank which i need to update as per full_name.I have more than 1000 distinct full_name.I want to update the ccolumn according to different logic which all depends upon underscore(_) in full_name.to illustrate i have full_name= 'james_9333_mathew_rohn' it means full_name has more than 1 underscore in name then i want nick_name to be ''james_rohn'.but if there is underscore at last in full_name( 'james_9333_mathew_rohn_' ) then result could be either 'james_rohn' or 'james_rohn_' both are fine for this case.when we have only 1 undersore like 'james_rohn' then nick_name should be 'james_rohn' and but if there is undersocre at last like 'james_rohn_' then result could be either either 'james_rohn' or 'james_rohn_' both are fine.please help me to solve this.


I want my final table to be


insert into test_name values( 'james_9333_mathew_rohn','james_rohn')
,('james_9333_mathew_rohn_','james_rohn_'),
('james_rohn','james_rohn'),
('james_rohn_','james_rohn_')

select *, LEN(full_Name) - LEN(replace(full_name, '_', '')) as UnderscoreCount 
from test_name

select *, LEN(full_Name) - LEN(replace(full_name, '_', '')) as UnderscoreCount
, LEFT(full_name, charindex('_', full_name)) + left(ltrim(replace(reverse(full_name), '_', ' ')), charindex(' ', ltrim(replace(reverse(full_name), '_', ' '))))
from test_name

Luis Cazares (9/10/2012)

---

Maybe this modification to Sean's code will work better:

select *, 
	LEFT(full_name, charindex('_', full_name)) + RIGHT(full_name, charindex('_', reverse(full_name),2) - 1)
from test_name

Sean Lange (9/10/2012)

---

[quote][b]
You know simply repeating a vague description does nothing to help me understand it.

dwain.c (9/11/2012)

---

"Insanity is doing the same thing over and over again and expecting different results."
-- Albert Einstein