need to udate table entry with number containing a *

[Billz66]

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



If I never did anything I'd never done before , I'd never do anything.....

 

[pjw001]

Is there an error message?

 

[fredericofonseca]

if the datatype of Dest in BCheck is not a Char (nvarchar/varchar/char) it will never work.
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

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[fredericofonseca]

Thanks Andy - corrected post.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[SkipVought]

@Billz66, just a general comment that might clarify your thinking.

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

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"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!

 

[Chris Miller]

Well, its simpler.

this works to change to 33333:
update [NumberDB].[dbo].[BCheck] set Dest =33333
where SiteID =90000

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

 

[Chris Miller]

I also won't be surprised, Billz66, if you tell me that the software you use displays phone numbers or parts of them with an asterisk in front, but then that's not done by changing that column storing a number. You should look out for a column of type bit or a char(1) that's separate from this column and stores that and the software then displays the asterisk or not.

Chriss

 

[Andrzejek]

Not so fast.
Billz66 can introduce a Computed Column in his [tt][NumberDB].[dbo].[BCheck][/tt] table:

[pre]
Dest NewField
33333 33333
99999 *99999[/pre]

where the NewField is [tt]('*' + Dest)[/tt] 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...?)

[pre]
OldDest Dest
33333 33333
99999 *99999[/pre]

Would that work

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Chris Miller]

Good idea, but also that would only show up in a software or in a report, after it was adapted to this change.
So that problem is never just solved with a database change only.

Chriss

 

[Chris Miller]

Let me start all over without any sarcasm. How about giving more background first, Ballz66.

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

 

[Billz66]

thanks for all the replies , first time i have posted in this forum - i normally stay in the mitel one

- 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.....

 

[Chris Miller]

Thanks for the feedback Billz66,

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:

update [NumberDB].[dbo].[BCheck] set Dest =33333 [highlight #FCE94F]<- no string delimiters here[/highlight]
where SiteID =90000

This must have been

update [NumberDB].[dbo].[BCheck] set Dest = '33333'
where SiteID = 90000

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