...it's me...eRiCk

once a friend of mine asking how to replace space character between words using query..i say it's easy just use RTRIM and LTRIM function but again he said what about replace the character after that? RTRIM and LTRIM only remove leading and trailing spaces, let say u have this word ' a b c d ', using RTRIM and LTRIM only make it something like this 'a b c d'. what about spaces after the word 'a', 'b', so on?? i thought it was easy, using REPLACE then but that's not what he want because REPLACE only make the word is non-readable so i'm thinking using something like this DECLARE @INITIAL VARCHAR(100) DECLARE @Rest VARCHAR(100) DECLARE @Final VARCHAR(100) DECLARE @CharToFind VARCHAR(5) DECLARE @ReplaceChar VARCHAR(5) DECLARE @found INT SET @Initial = ' a,,b,,,c,,,,,,,,,,d,,,,,,,, ' SET @Final = '' SET @CharToFind =',' SET @ReplaceChar ='.' SELECT @Rest = REPLACE(RTRIM(LTRIM(@Initial)), @CharToFind, @R

it can be done in SQL SERVER 2005 and later using CTE (common table expression) DECLARE @Table1 AS TABLE ( LevelID INT NOT NULL PRIMARY KEY, Position NVARCHAR(50) NOT NULL, ReportingLevelID INT ) INSERT INTO @Table1 SELECT 1, 'Chief Executive Officer', NULL INSERT INTO @Table1 SELECT 2, 'Senior Director - Development', 1 INSERT INTO @Table1 SELECT 3, 'Senior Director - Finance', 1 INSERT INTO @Table1 SELECT 4, 'Senior Director - Human Resources', 1 INSERT INTO @Table1 SELECT 5, 'Product Development Manager', 2 INSERT INTO @Table1 SELECT 6, 'Project Lead', 5 INSERT INTO @Table1 SELECT 7, 'QA Lead', 5 INSERT INTO @Table1 SELECT 8, 'Documentation Lead', 5 INSERT INTO @Table1 SELECT 9, 'Developers', 6 INSERT INTO @Table1 SELECT 10, 'Testers', 7 INSERT INTO @Table1 SELECT 11, 'Writers', 8 INSERT INTO @Table1 SELECT 12, 'Accountants', 3 INSERT INTO @Table1 SELECT 13, 'HR Profes