Need assist on TSQL query

I've a Table called Employee where Grade column has value as given below.

Ex:  U#@H,UMS

I would like to  implement below logic:

  Get All characters before the comma(U#@H)
  Search each and every character one by one in the string after comma (UMS)
  If the character is found then delete it (U should be removed)

and the Expected output is  U#@H,MS

Like above logic I would like to perform all the value in the column.

How to achieve this? Please do assist.
LVL 16
Easwaran ParamasivamAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Explain how you determined that the U should be deleted.  
Character after the comma?
0
Brendt HessSenior DBACommented:
This is a bit tricky, but I can perform this in two functions (and a table).

The process is going to use a tally table (a table that is only a list of numbers) to compare the two substrings for matching values, then use the output of that in another function that will remove the duplicates.

First, create the tally table.  This is a small version with only values from 1 to 1024 in the table.

CREATE TABLE TallySmall (N smallint PRIMARY KEY CLUSTERED)

INSERT INTO TallySmall VALUES (1)

DECLARE @i int, @m smallint

SET @i = 0

WHILE @i < 10
BEGIN
	SELECT @m= MAX(N) FROM Tallysmall
	INSERT INTO TallySmall
	SELECT N+@m
	FROM TallySmall
	
	SET @i = @i + 1
END

Open in new window

Then, create a function to convert a string passed in into a one column table of values.

CREATE FUNCTION dbo.TableString ( @s varchar(128) )
RETURNS TABLE
AS
	RETURN SELECT SUBSTRING(@s, N, 1) AS ltr 
		FROM TallySmall 
		WHERE n <= LEN(@s)

Open in new window

Finally, create the function to strip out duplicates and return the full string.

CREATE FUNCTION dbo.DedupeGrade  ( @s varchar(16) )
RETURNS varchar(16)
AS
BEGIN
	DECLARE @s1 varchar(16), 
		@s2 varchar(16), 
		@ptr int,
		@match varchar(16), 
		@out varchar(16)
		
	SELECT @ptr = CHARINDEX(',', @s)
	IF @ptr IN (0, 1) OR @ptr = LEN(@s)		-- No comma, or comma in first or last position
	BEGIN
		SET @out = @s  -- the entire string is returned, since there are no duplicates
	END
	ELSE
	BEGIN
		SELECT @s1 = SUBSTRING(@s, 1, @ptr)	 -- include the comma in @s1
		SELECT @s2 = SUBSTRING(@s, @ptr+1, 16)

		SET @match = ''

		SELECT @match = @match + s.ltr
		FROM (
			SELECT DISTINCT s2.ltr
			FROM dbo.TableString(@s1) AS s1
			INNER JOIN dbo.TableString(@s2) AS s2
				ON s1.ltr = s2.ltr
			) s

		-- At this point, @match contains the characters that match between the 
		--		two sections of the string, if any
		IF @match > ''		-- If there are matches
		BEGIN
			SET @ptr = LEN(@match)
			WHILE @ptr > 0
			BEGIN
				SELECT @s2 = REPLACE(@s2, SUBSTRING(@match, @ptr, 1)	, '')
				SET @ptr = @ptr-1
			END
			SET @out = @s1 + @s2 
		END
		ELSE
			SET @out = @s	-- No matches, return the whole string
	END
	RETURN @out
END

Open in new window

Now, if you run this on your sample string above:

SELECT dbo.DedupeGrade('U#@H,UMS')

You will have this table returned:

(No column  name)
U#@H,MS

This is written to handle multiple instances of a character in the second segment.... just in case.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Easwaran ParamasivamAuthor Commented:
Excellent!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.