September 19, 2016 at 1:00 am
I currently have a column "Description" which has a zip-code in it in Table 'Client'. I have another table that has a column with 'City" and "Zip-code".
I want to update the Table 'Client' so that the column 'Description' shows the city and zip-code jointed with a hyphen between them.
I have tried using
set [Description] = REPLACE ([Description], City + ' - '+ Description)
but I get an error message 'The replace function requires 3 argument(s).'
I do not want to create a different view or table as the table is used in specific processes and cannot be changed to a new table/view name.
Any suggestions?
September 19, 2016 at 2:13 am
Does this work?
UPDATE c
SET [Description] = City + ' - ' + [Zip-Code]
FROM Client c
JOIN cityTable t ON c.[Description] = t.[Zip-Code]
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 19, 2016 at 2:14 am
Btw, if you would like to use the REPLACE function, it should be something like this:
REPLACE([Description],[Description],City + ' - ' + [Zip-Code])
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 19, 2016 at 8:08 pm
Koen
Sorry forgot to say the Description column also has additional data after the City so cannot just bring in the City and Zip-code from another table. Need to use the existing Description and append the zip-code at the beginning.
September 19, 2016 at 9:53 pm
les.61 (9/19/2016)
KoenSorry forgot to say the Description column also has additional data after the City so cannot just bring in the City and Zip-code from another table. Need to use the existing Description and append the zip-code at the beginning.
set [Description] = City + ' - ' + [Zip-Code] + ' - '+ Description
_____________
Code for TallyGenerator
September 19, 2016 at 9:57 pm
Sergiy (9/19/2016)
les.61 (9/19/2016)
KoenSorry forgot to say the Description column also has additional data after the City so cannot just bring in the City and Zip-code from another table. Need to use the existing Description and append the zip-code at the beginning.
set [Description] = City + ' - ' + [Zip-Code] + ' - '+ Description
Or, may be this:
set [Description] = REPLACE(Description, City, City + ' - ' + [Zip-Code])
.....
WHERE [Description] not like '%' + City + ' - ' + [Zip-Code] + '%'
"WHERE" condition to make sure description does not have zip code included already
_____________
Code for TallyGenerator
September 19, 2016 at 10:47 pm
Many thanks Sergiy
If I run the SET command I get an "incorrect Syntax near '=' " message.
I ran
Select (City + ' - ' + [Zip-Code] + ' - '+ Description) as test
and the result was correct but just will not seem to do the SET function.
I tried the Replace option and if I just do the REPLACE it works but when I add the SET command I get the error message above.
Any suggestions?
September 19, 2016 at 11:51 pm
les.61 (9/19/2016)
Many thanks SergiyIf I run the SET command I get an "incorrect Syntax near '=' " message.
I ran
Select (City + ' - ' + [Zip-Code] + ' - '+ Description) as test
and the result was correct but just will not seem to do the SET function.
I tried the Replace option and if I just do the REPLACE it works but when I add the SET command I get the error message above.
Any suggestions?
Can you post the actual UPDATE query that you are using along with the actual error message?
That might help in finding the cause for the error.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 20, 2016 at 12:01 am
les.61 (9/19/2016)
Many thanks SergiyIf I run the SET command I get an "incorrect Syntax near '=' " message.
I ran
Select (City + ' - ' + [Zip-Code] + ' - '+ Description) as test
and the result was correct but just will not seem to do the SET function.
I tried the Replace option and if I just do the REPLACE it works but when I add the SET command I get the error message above.
Any suggestions?
SET is a part of UPDATE statement.
_____________
Code for TallyGenerator
September 20, 2016 at 1:20 am
The script that works is
SELECT
d.description, d.account, c.city, c.state, c.zipcode,
--set [Description] =
Replace(d.Description, d.Description ,(c.zipcode + ' - ' + d.Description) )
FROM [DocumentManagement].[dbo].[LesDescription] as d
inner join [DocumentManagement].[dbo].[LesCities] as c
on d.account = c.city
The result is the columns plus a column with "No column name" as follows (the **** is so that I could show which headings match which columns)
description*********************account*****city*******state***********zipcode*********(No column name)
Sydney - Express Service - 1598***Sydney*****Sydney***New South Wales****2000****2000 - Sydney - Express Service - 1598
If I change the script to
--SELECT
--d.description, d.account, c.city, c.state, c.zipcode,
set [Description] = Replace(d.Description, d.Description ,(c.zipcode + ' - ' + d.Description) )
FROM [DocumentManagement].[dbo].[LesDescription] as d
inner join [DocumentManagement].[dbo].[LesCities] as c
on d.account = c.city
I get the error message
"Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'Description'."
What I am trying to do is get the "(No column name)" data to replace the "Description" data
September 20, 2016 at 1:23 am
You forgot a part of the update statement.
UPDATE d
set [Description] = Replace(d.Description, d.Description ,(c.zipcode + ' - ' + d.Description) )
FROM [DocumentManagement].[dbo].[LesDescription] as d
inner join [DocumentManagement].[dbo].[LesCities] as c
on d.account = c.city
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 20, 2016 at 1:29 am
You can do this without using REPLACE as well
UPDATE d
set d.[Description] = c.zipcode + ' - ' + d.Description
FROM [DocumentManagement].[dbo].[LesDescription] as d
inner join [DocumentManagement].[dbo].[LesCities] as c
on d.account = c.city
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 20, 2016 at 1:45 am
Koen and Kingston
Many, many, many thanks. It works a treat.
Also to Sergiy, thanks as I have included the WHERE code you suggested to make sure it does not keep adding to itself.
:satisfied::-)
September 21, 2016 at 8:00 pm
The error message is pretty straightforward; the replace () has to have the target, the old string and the new string.
You really need to clean up the data instead of trying to kludge it into shape. I am sorry you have only one client; I hope business gets better. Did you mean "clientele" or some other collective or plural noun to show this is a set, and not a single record in the file system? Does "city" actually mean "city_name" or is there another attribute property that you left off (look at the ISO 11179 naming rules)? Also, there is no such thing as a generic description; it has to be of something in particular.
In a correctly normalized table, each distinctive attribute will have its own column. This means that the ZIP Code ought to be in a column declared as "zip_code CHAR(5) NOT NULL CHECK (zip_code LIKE'[0-9][0-9][0-9][0-9][0-9]')" so that you have some data integrity. Likewise, the city name should be in its own column and it needs a two-letter state code. Bit of trivia: the reason The Simpsons cartoon characters live in Springfield is that there are so many Springfields in the United States. This trick was originally used on an old TV show called "Father Knows Best" and became part of TV lore 😎
You will probably want to get a CASS validation tool to make sure the city, state and zip codes are all correct. Clean it up in the front and then put it in the database.
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply