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_')
You know simply repeating a vague description does nothing to help me understand it.
You can check for the existence of more than 1 underscore pretty easily.
select *, LEN(full_Name) - LEN(replace(full_name, '_', '')) as UnderscoreCount
from test_name
This is not the most efficient code but I THINK it might be what you are looking for.
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
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/