CHAR Cleanser (With Parameter)

Yeayyyyyyyyy, first post in 2017.

A simple tips how to clean word inside string in SQL Server (I’m Using SQL Server 2005) with parameter.

IF OBJECT_ID('TEMPDB.DBO.#TEMP') IS NOT NULL
 DROP TABLE #TEMP
 
SELECT *
INTO #TEMP
FROM
(
	SELECT 'PT' COL1 UNION ALL
	SELECT 'PT.' COL1 UNION ALL
	SELECT 'PT ' COL1 UNION ALL
	SELECT 'CV' COL1
) BBB
ORDER BY LEN(COL1) DESC
 
IF OBJECT_ID('TEMPDB.DBO.#TEMP') IS NOT NULL
 DROP TABLE #DATA
 
SELECT *
INTO #DATA
FROM
(
	SELECT 'PT CONTOH1' CNAME, CAST('' AS VARCHAR) CNAME_AFTER UNION ALL
	SELECT 'PT CONTOH2' CNAME, CAST('' AS VARCHAR) CNAME_AFTER UNION ALL
	SELECT 'PT CONTOH3' CNAME, CAST('' AS VARCHAR) CNAME_AFTER UNION ALL
	SELECT 'CV CONTOH4' CNAME, CAST('' AS VARCHAR) CNAME_AFTER UNION ALL
	SELECT 'PT CONTOH5' CNAME, CAST('' AS VARCHAR) CNAME_AFTER UNION ALL
	SELECT 'CV CONTOH6' CNAME, CAST('' AS VARCHAR) CNAME_AFTER UNION ALL
	SELECT 'PT CONTOH7' CNAME, CAST('' AS VARCHAR) CNAME_AFTER UNION ALL
	SELECT 'CV CONTOH8' CNAME, CAST('' AS VARCHAR) CNAME_AFTER UNION ALL
	SELECT 'CV CONTOH9' CNAME, CAST('' AS VARCHAR) CNAME_AFTER UNION ALL
	SELECT 'PT CONTOH10' CNAME, CAST('' AS VARCHAR) CNAME_AFTER UNION ALL
	SELECT 'CONTOH11' CNAME, CAST('' AS VARCHAR) CNAME_AFTER UNION ALL
	SELECT 'CONTOH12' CNAME, CAST('' AS VARCHAR) CNAME_AFTER UNION ALL
	SELECT 'CONTOH13' CNAME, CAST('' AS VARCHAR) CNAME_AFTER UNION ALL
	SELECT 'PT. CONTOH14' CNAME, CAST('' AS VARCHAR) CNAME_AFTER
) A
 
-- QUERY BEFORE
SELECT *
FROM #DATA A
INNER JOIN #TEMP B ON SUBSTRING(CNAME, 1, LEN(COL1)) = COL1
 
-- QUERY UPDATE
UPDATE A
SET
CNAME_AFTER =
 CASE
  WHEN SUBSTRING(CNAME, 1, LEN(COL1)) = COL1 THEN RTRIM(LTRIM(RIGHT(CNAME, LEN(CNAME) - LEN(COL1))))
  ELSE CNAME 
 END
FROM #DATA A
INNER JOIN #TEMP B ON SUBSTRING(CNAME, 1, LEN(COL1)) = COL1
 
-- QUERY AFTER
SELECT *
FROM #DATA

CMIIW.

Best Regards

Leave a Reply

Your email address will not be published. Required fields are marked *

Afiseaza emoticoanele Locco.Ro