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