need to udate table entry with number containing a *
need to udate table entry with number containing a *
(OP)
phone system doing sql query to work out transfer destinations
previously was using number 99999
we need to change this to be *99999 to send it to a mailbox
having issues trying to work out how to do the update
this works to change to 33333
update [NumberDB].[dbo].[BCheck] set Dest =33333
where SiteID =90000
this fails
update [NumberDB].[dbo].[BCheck] set Dest =*99999
where SiteID =90000
previously was using number 99999
we need to change this to be *99999 to send it to a mailbox
having issues trying to work out how to do the update
this works to change to 33333
update [NumberDB].[dbo].[BCheck] set Dest =33333
where SiteID =90000
this fails
update [NumberDB].[dbo].[BCheck] set Dest =*99999
where SiteID =90000
If I never did anything I'd never done before , I'd never do anything.....
RE: need to udate table entry with number containing a *
RE: need to udate table entry with number containing a *
and if it is one of the above then you need to add single quotes around both the 90000 and the *99999 for it to work.
Regards
Frederico Fonseca
SysSoft Integrated Ltd
www.syssoft-int.com
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?
RE: need to udate table entry with number containing a *
Regards
Frederico Fonseca
SysSoft Integrated Ltd
www.syssoft-int.com
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?
RE: need to udate table entry with number containing a *
There are NUMBERS that you will routinely perform arithmetic on and then there are IDENTIFIERS that might contain all numeric characters but you would never raise to a power or find a quotient.
Numeric only IDENTIFIERS ought never be stored as NUMERIC data types. Different logic is performed on strings than on numbers.
FAQ68-6659: When is a NUMBER not a NUMBER?
So when some unthinking techie has set up IDENTIFIERS as numeric values instead of TEXT values, just change your way of thinking about the logic of data manipulation and remember that numeric IDENTIFIERS are really numeric text characters.
Skip,
for a NUance!
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!
RE: need to udate table entry with number containing a *
That proves Dest is a numeric field. And you cant store a character like '*' into it. Because if it was a character field that update would have errored.
So that need goes with a structural database change of a column that never should have been numeric - but is. It has to be altered to a character type column. And then you can do as fredericofonseca said. Not with the table you have now.
PS: Before you just do that with SSMS, for example, realize that changing a data type also needs code changes and a new version of software. It is easy to change a column from whatever it is now (bigint, numeric, whatever) to an appropriate length varchar, but you'll cause the software to error.
It's a design flaw and, clearly, the poor fellow who choose the wrong data type has to be executed so it never happens again. Which, of course, is the hardest part of all of this.
Chriss
RE: need to udate table entry with number containing a *
Chriss
RE: need to udate table entry with number containing a *
Billz66 can introduce a Computed Column in his [NumberDB].[dbo].[BCheck] table:
where the NewField is ('*' + Dest) with some additional criteria if that rule applies to some and not other records.
He can do some other 'juggling' of fields: rename Dest to something else (like OldDest) and have his new Computed Column named Dest (no changes to any code using this data...?)
Would that work
---- Andy
"Hmm...they have the internet on computers now"--Homer Simpson
RE: need to udate table entry with number containing a *
So that problem is never just solved with a database change only.
Chriss
RE: need to udate table entry with number containing a *
Are you trying to solve a problem by changing data of a commercial application?
Or is that software custom-made for your company or was it built in-house by an IT department?
If your only hands are on the data, then perhaps you can also change reports, but likely not the application itself.
I can just tell you that your wish to introduce an asterisk with just a slightly different query isn't possible, unfortunately.
Chriss
RE: need to udate table entry with number containing a *
- before i checked back here I went back and tried some other entries and *999 worked
Then I checked the structure of the table (was created by a previous maintainer)
- this in hind site is something i obviously should have checked first (i'm a phone guy not a sql guy)
column i was writing to was set as varchar(5)
Once i changed that to varchar(7)
my update worked
seems that the entry of *99999 needed 7 characters to store it
do special characters need extra character to store them ?
If I never did anything I'd never done before , I'd never do anything.....
RE: need to udate table entry with number containing a *
Good news for the designer of that, his execution was canceled. Though 5 chars maximum is only okay for things like a prefix, not a whole phone number, but I guess that's what it's for.
*99999 is 6 characters, if I count right, and doesn't fit into varchar(5), so it was originally not foreseen to enable this length. Since it is a char field it can store *, your error is to write this string *99999 as is, in code a string has to be delimited with quotes, in case of MS SQL Server single quotes, so the string needs to be provided as '*99999'. Changing to varchar(6) would have been enough for that, but you never know when you need 7 chars.
Since the software using that is likely not your own, you're a bit lucky this extension did work out okay, code might only expect and accept 5 chars, if the database is designed like it was. The length extension of a field is often working out fine, though. A problem might arise if someday some overall complete put together number is 1 character too long, but I guess you'll be fine.
The other thing then is that the query you said worked in the past, could not have been literally the query you or a colleague did. Because the data type is varchar and in your query, you didn't use string delimiters:
CODE
update [NumberDB].[dbo].[BCheck] set Dest =33333 <- no string delimiters here where SiteID =90000
This must have been
CODE
If coding is not your thing, then please don't guess such things, you can really demolish data. I once saved the day of an IT support guy who made an UPDATE and forgot to have a WHERE clause. That means, his update updated all rows of a table, and we had to restore that data.
You have simpler means in the SQL Server management studio, you can edit a table and then modify data displayed in a grid. There you have WYSIWYG (what you see is what you get), in code you have to know the different notations of numbers, strings, and other data types.
Chriss