Newbie Query Help
-
I have to change data in a column to reflect new municipality names, ex: [city] TWP to [city] TOWNSHIP and replace all [city] BORO with [city] BOROUGH.
But all of the data is different. Some names are as [city]TWP\[city]BORO or [city] TWP\[city] BORO and I'm not sure on how to update this data. I ran across the case and replace commands, but am unsure as to how to use these?
Any help would be greatly appreciated!!!
-
untested, but it should be something like this...
update mytable
set city =
case
when city = 'TWP' then 'TOWNSHIP'
when city = 'BORO' then 'BOROUGH'
else city
end
**ASCII stupid question, get a stupid ANSI !!!** -
If sushila's suggestion doesn't help, provide us some sample data and that will help us to provide a better solution.
-SQLBill
-
I actually have a question and I am wondering if anyone out there can help me. I have multiple insert statements that need to be executed. Each insert statement is exactly the same except one value; is there any way I can do this more efficiently? (I know what the value is going to be each time and it is not dependant on anything.) Example: INSERT INTO CQT_ORGANIZATION_PRODUCT(ORP_ORG_ID, ORP_PRD_ID) SELECT Org_ID, '86' FROM CQT_ORGANIZATION (nolock) WHERE Org_ID not in (Select Orp_Org_ID From CQT_ORGANIZATION_PRODUCT (nolock)) INSERT INTO CQT_ORGANIZATION_PRODUCT(ORP_ORG_ID, ORP_PRD_ID) SELECT Org_ID, '88' FROM CQT_ORGANIZATION (nolock) WHERE Org_ID not in (Select Orp_Org_ID From CQT_ORGANIZATION_PRODUCT (nolock)) INSERT INTO CQT_ORGANIZATION_PRODUCT(ORP_ORG_ID, ORP_PRD_ID) SELECT Org_ID, ‘92’ FROM CQT_ORGANIZATION (nolock) WHERE Org_ID not in (Select Orp_Org_ID From CQT_ORGANIZATION_PRODUCT (nolock)) There just has to be a way to combine these??…. Any suggestions you could give me I’d be greatly appreciated!!!
-
Glorianni - it'd have been better if you hadn't piggy-backed on this post simply because you stand chances of your post getting overlooked this way...
One solution that I can think of is to first do an insert:
INSERT INTO CQT_ORGANIZATION_PRODUCT(ORP_ORG_ID)
SELECT Org_ID
FROM CQT_ORGANIZATION
WHERE Org_ID not in (Select Orp_Org_ID From CQT_ORGANIZATION_PRODUCT)
& then do an update...
UPDATE CQT_ORGANIZATION_PRODUCT
SET ORP_PRD_ID =
CASE
WHEN ORP_ORG_ID = ### then 86
WHEN ORP_ORG_ID = ### then 92 etc...
ELSE 0
END
**ASCII stupid question, get a stupid ANSI !!!** -
I wasn't sure how to post a new forum sorry.
But thank you for your suggestion.
-
if you browse the forum list - they are separated by topic - "Newbies"; "Administration"; "T-Sql" etc...
pick the category you think that your question falls under..and there is a button called "new thread" on the top left of the screen that will let you start a new thread...
**ASCII stupid question, get a stupid ANSI !!!** -
I didn't know that you knew that button Sue
. -
RG - most of the time I don't let on how much I really know!!!
**ASCII stupid question, get a stupid ANSI !!!** -
Getting back to cspangler's original question, I suspect you don't want to replace the entire contents of City, just the abbreviations used. If that's the case, I think you want something like this:
Update MyTable
set City =
case
when charindex('TWP', City) > 0
then REPLACE(City, 'TWP', 'TOWNSHIP')
when charindex('BORO', City) > 0
then REPLACE(City, 'BORO', 'BOROUGH')
else City
end
You'll need a 'when' for everything you want to change, and you may need some spaces around what you're changing, in case you have a city like 'Glasboro'. This will change every record in the table. There's probably a better way to limit it to the ones that need to be changed, but the only way I can think of is like this, which is somewhat unwieldy if you're changing a lot of abbreviations.
Update MyTable
set City =
case
when charindex('TWP', City) > 0
then REPLACE(City, 'TWP', 'TOWNSHIP')
when charindex('BORO', City) > 0
then REPLACE(City, 'BORO', 'BOROUGH')
end
where City <>
case
when charindex('TWP', City) > 0
then REPLACE(City, 'TWP', 'TOWNSHIP')
when charindex('BORO', City) > 0
then REPLACE(City, 'BORO', 'BOROUGH')
Mattie
-
MattieNH,
you hit the nail on the head!! Thanks for the great help!
I was wondering if I could ask a question concernig the script? When you call the 'when'command :'when charindex('TWP', City) > 0', you use charindex and >0. I was hoping you could me a quick synopsis on this...
thanks again!
-
I'm hoping (actually, I'm pretty sure) that 'when charindex('TWP', City) > 0' is SQLese for 'when the starting location of the text string 'TWP', in column City, is not zero'. Greater than zero means the string was found, zero means it wasn't. Take a look at Books On Line, usually referred to in these forums as BOL. This is part of what it says about charindex
CHARINDEX
Returns the starting position of the specified expression in a character string.
Syntax
CHARINDEX ( expression1 , expression2 [ , start_location ] )Arguments
expression1
Is an expression containing the sequence of characters to be found. expression1 is an expression of the short character data type category.
expression2
Is an expression, usually a column searched for the specified sequence. expression2 is of the character string data type category.
start_location
Is the character position to start searching for expression1 in expression2. If start_location is not given, is a negative number, or is zero, the search starts at the beginning of expression2.
Return Types
int
Glad to help.
Mattie
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply