Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

need to udate table entry with number containing a *

need to udate table entry with number containing a *

need to udate table entry with number containing a *

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

RE: need to udate table entry with number containing a *

Is there an error message?

RE: need to udate table entry with number containing a *

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.


Frederico Fonseca
SysSoft Integrated Ltd

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 *

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


glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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 *

Well, its simpler.

Quote (Billz66)

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.


RE: need to udate table entry with number containing a *

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.


RE: need to udate table entry with number containing a *

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

Dest    NewField
33333    33333
99999   *99999 

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

OldDest  Dest
33333    33333
99999   *99999 

Would that work ponder

---- Andy

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

RE: need to udate table entry with number containing a *

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.


RE: need to udate table entry with number containing a *

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.


RE: need to udate table entry with number containing a *

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

RE: need to udate table entry with number containing a *

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 <- no string delimiters here
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.


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close