Change Indentity Increment

I need to change the seed and increment for an identity field in an existing
table and would like to make the change in a script rather then in Enterpris
e
Manager. I understand that I can use DBCC CHECKIDENT to reseed the field.
Is there a function to change the identity seed? My question applies to MS
SQL 2000.
Thanks in advance for any help.
JeremySounds scary!
Without thinking, one way to do it is to move the data off to a temporary
location, truncate your table, alter the ID column to be identity(x,y), and
then repopulate it (in the appropriate order).
I repeat my earlier comment - sounds scary!
Rob|||Yeah, especially if you have any foreign key constraints set up...
"Rob Farley" <RobFarley@.discussions.microsoft.com> wrote in message
news:4F74A9C4-559C-49FF-A36E-159CBF2022AF@.microsoft.com...
> Sounds scary!
> Without thinking, one way to do it is to move the data off to a temporary
> location, truncate your table, alter the ID column to be identity(x,y),
> and
> then repopulate it (in the appropriate order).
> I repeat my earlier comment - sounds scary!
> Rob|||Jeremy - do you mind explaining WHY you want to do such a thing?|||Thanks for the reply Rob.
One the requirements I've been given for this database project is that new
identity values in TableA should be even, and in TableB should be odd.
"Rob Farley" wrote:

> Jeremy - do you mind explaining WHY you want to do such a thing?|||What about the records that currently exist? Are you going to divvy them up
between even records in one table, odd in the other? Or are you going to
re-number all existing records? This whole thing sounds a little painful
from here...
"jeremy@.nospamwardlawclaims.com"
< jeremynospamwardlawclaimscom@.discussions
.microsoft.com> wrote in message
news:1A3467C3-CFF3-427A-A4FB-EE2C8FEA58A1@.microsoft.com...
> Thanks for the reply Rob.
> One the requirements I've been given for this database project is that new
> identity values in TableA should be even, and in TableB should be odd.
> "Rob Farley" wrote:
>|||Thanks for the reply. The identity values for existing rows would remain th
e
same. New records for TableA would have an even identity value with a new
seed of 1002 and increment of 2. TableB would have a new seed of 1002 and
increment 2 to have odd identity values for new records.
"Michael C#" wrote:

> What about the records that currently exist? Are you going to divvy them u
p
> between even records in one table, odd in the other? Or are you going to
> re-number all existing records? This whole thing sounds a little painful
> from here...
>
> "jeremy@.nospamwardlawclaims.com"
> < jeremynospamwardlawclaimscom@.discussions
.microsoft.com> wrote in message
> news:1A3467C3-CFF3-427A-A4FB-EE2C8FEA58A1@.microsoft.com...
>
>|||OK. Do both of these tables currently exist, and do both currently have
both even and odd identity values in them? Would it be easier to just add
another column to your PK (CHAR(1) possibly) indicating different identity
value sources? This solution just sounds a little shaky... what happens if
they add a third table in the future? Re-define all identities to +3
beginning with record x,xxxx?
Thanks.
"jeremy@.nospamwardlawclaims.com"
< jeremynospamwardlawclaimscom@.discussions
.microsoft.com> wrote in message
news:3603F85C-41FB-4930-8E7A-6822E763058F@.microsoft.com...
> Thanks for the reply. The identity values for existing rows would remain
> the
> same. New records for TableA would have an even identity value with a new
> seed of 1002 and increment of 2. TableB would have a new seed of 1002 and
> increment 2 to have odd identity values for new records.
> "Michael C#" wrote:
>|||To me it sounds like you actually want a view, with an insert trigger. The
trigger puts the inserted values into either TableA or TableB, depending on
which one has the higher ID field. But the view shows all of them.
This is the type of thing you might do if you wanted to set up replication
scenarios, or divide the tables between two separate databases and devices.
I think you create the new tables from scratch. Create the view (which is
just a 'select * from TableA union all select * from TableB') and the
trigger, and then populate the view from your old table. Let the trigger
handle the distribution of records.
Of course, you will need to make sure that the trigger handles the situation
where there are a whole bunch of rows in the 'Inserted' table. But there are
easy ways around this.
Rob

Change Format of Dates

I don't know why my company did this, but dates are being stored in a char field within our database. Meanwhile, I've been setting up new pages using datetime.

That's just a little back story. My question is, is it possible to change the format of all dates in the table from yyyy/MM/dd to MM/dd/yyyy in the char field? I'm just trying to think of an easier way to change a thousand or so records instead of doing it manually.

Thanks.

Try this:

string temp ="2006/12/16";
string[] field = temp.Split(("/").ToCharArray());
string reversedate = field[2] +"/" + field[1] +"/" + field[0];
DateTime newdate = DateTime.Parse(reversedate);

|||

Or better still, run this SQL

Update table set newdatefield = parsename(replace(oldchardate, '/', '.'), 2) + '/' + parsename(replace(oldchardate, '/', '.'), 1) + '/' + parsename(replace(oldchardate, '/', '.'), 3)

|||

try this logic

print

convert(varchar(20),convert(datetime,'2002/02/23',111),101)

so you can just do update on your table date field content by

update YourTable
set datefield=convert(varchar(20),convert(datetime,datefield,111),101)

Thanks

|||

I ended up using this logic in a SQL statement:

datefield=right(datefield, 5)&'/'&left(datefield, 4)

sql

Change field with numeric and alphanumeric to numeric

I have a text field with records that mostly contain numbers, but in some of the records the field is alphanumeric, such as P234032. I want to convert the field to numeric, and just allow any alphanumeric fields to be ignored, and become null.

I'm used to doing this in Microsoft Access - it just ignores those strings and they become null - how can I do this in SQL Server 2000?

Thanks,

RandyDoes this help

set nocount on
go
create table #test (col1 char(10), col2 char(10))
go
insert #test values ('BU89090', 'record 1' )
insert #test values ('GHJ9213', 'record 2' )
insert #test values ('79898', 'record 3' )
insert #test values ('89067', 'record 4' )
insert #test values ('09889067', 'record 5' )
go

select *
from #test
go
select convert(int, case when isnumeric(col1) = 1 then col1 else NULL end) as intCol1, col2
from #test
go
drop table #test

Output
col1 col2
---- ----
BU89090 record 1
GHJ9213 record 2
79898 record 3
89067 record 4
09889067 record 5

intCol1 col2
---- ----
NULL record 1
NULL record 2
79898 record 3
89067 record 4
9889067 record 5|||achorozy,

Thanks for the reply! I haven't tried this yet, but will have a chance first thing tomorrow morning, and will let you know. The SQL Server 2000 course that I just completed was pretty thorough, but seems like it didn't cover the nitty gritty things that I need to know right now.

This is sure a different animal than MS Access!

Thanks again,

Randy

Change field size

SQL 2005
I have a table with a number of nvarchar fields. One field in the midde was
1000 in length. I ran a query with:
ALTER TableName
ALTER ColumnName nvarchar(2000).
This apeared to increase the field size to 2000 (as seen in Server
Management Studio), but it will still not store any more than 1000 characters.
Have I done it incorrectly.
There appears to be still space in my record as I can add a field at the end
2000 long and that works correctly.
Thanks,
Steve.
Why do you say it only stores 1000 characters? Is it because what you are
seeing when you select it? If so then change the max size of the columns
characters returned in the options menu to support more. What does
MAX(DATALEN(YourColumn)) say for that column?
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Sawlmgsj" <Sawlmgsj@.discussions.microsoft.com> wrote in message
news:1F7CC5B5-47B9-41B4-BD89-14263656BC0E@.microsoft.com...
> SQL 2005
> I have a table with a number of nvarchar fields. One field in the midde
> was
> 1000 in length. I ran a query with:
> ALTER TableName
> ALTER ColumnName nvarchar(2000).
> This apeared to increase the field size to 2000 (as seen in Server
> Management Studio), but it will still not store any more than 1000
> characters.
> Have I done it incorrectly.
> There appears to be still space in my record as I can add a field at the
> end
> 2000 long and that works correctly.
> Thanks,
> Steve.
>
|||Andrew - many thanks for your reply. That got me thinking and I checked the
(Delplhi) component I am using to link. It had field definitions in it and I
had forgotten to refresh it. Did that and now all is working correctly.
Thanks,
Steve.
"Andrew J. Kelly" wrote:

> Why do you say it only stores 1000 characters? Is it because what you are
> seeing when you select it? If so then change the max size of the columns
> characters returned in the options menu to support more. What does
> MAX(DATALEN(YourColumn)) say for that column?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Sawlmgsj" <Sawlmgsj@.discussions.microsoft.com> wrote in message
> news:1F7CC5B5-47B9-41B4-BD89-14263656BC0E@.microsoft.com...
>

Change field size

SQL 2005
I have a table with a number of nvarchar fields. One field in the midde was
1000 in length. I ran a query with:
ALTER TableName
ALTER ColumnName nvarchar(2000).
This apeared to increase the field size to 2000 (as seen in Server
Management Studio), but it will still not store any more than 1000 characters.
Have I done it incorrectly.
There appears to be still space in my record as I can add a field at the end
2000 long and that works correctly.
Thanks,
Steve.Why do you say it only stores 1000 characters? Is it because what you are
seeing when you select it? If so then change the max size of the columns
characters returned in the options menu to support more. What does
MAX(DATALEN(YourColumn)) say for that column?
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Sawlmgsj" <Sawlmgsj@.discussions.microsoft.com> wrote in message
news:1F7CC5B5-47B9-41B4-BD89-14263656BC0E@.microsoft.com...
> SQL 2005
> I have a table with a number of nvarchar fields. One field in the midde
> was
> 1000 in length. I ran a query with:
> ALTER TableName
> ALTER ColumnName nvarchar(2000).
> This apeared to increase the field size to 2000 (as seen in Server
> Management Studio), but it will still not store any more than 1000
> characters.
> Have I done it incorrectly.
> There appears to be still space in my record as I can add a field at the
> end
> 2000 long and that works correctly.
> Thanks,
> Steve.
>|||Andrew - many thanks for your reply. That got me thinking and I checked the
(Delplhi) component I am using to link. It had field definitions in it and I
had forgotten to refresh it. Did that and now all is working correctly.
Thanks,
Steve.
"Andrew J. Kelly" wrote:
> Why do you say it only stores 1000 characters? Is it because what you are
> seeing when you select it? If so then change the max size of the columns
> characters returned in the options menu to support more. What does
> MAX(DATALEN(YourColumn)) say for that column?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Sawlmgsj" <Sawlmgsj@.discussions.microsoft.com> wrote in message
> news:1F7CC5B5-47B9-41B4-BD89-14263656BC0E@.microsoft.com...
> > SQL 2005
> >
> > I have a table with a number of nvarchar fields. One field in the midde
> > was
> > 1000 in length. I ran a query with:
> >
> > ALTER TableName
> > ALTER ColumnName nvarchar(2000).
> >
> > This apeared to increase the field size to 2000 (as seen in Server
> > Management Studio), but it will still not store any more than 1000
> > characters.
> >
> > Have I done it incorrectly.
> >
> > There appears to be still space in my record as I can add a field at the
> > end
> > 2000 long and that works correctly.
> >
> > Thanks,
> > Steve.
> >
>

Change field size

SQL 2005
I have a table with a number of nvarchar fields. One field in the midde was
1000 in length. I ran a query with:
ALTER TableName
ALTER ColumnName nvarchar(2000).
This apeared to increase the field size to 2000 (as seen in Server
Management Studio), but it will still not store any more than 1000 character
s.
Have I done it incorrectly.
There appears to be still space in my record as I can add a field at the end
2000 long and that works correctly.
Thanks,
Steve.Why do you say it only stores 1000 characters? Is it because what you are
seeing when you select it? If so then change the max size of the columns
characters returned in the options menu to support more. What does
MAX(DATALEN(YourColumn)) say for that column?
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Sawlmgsj" <Sawlmgsj@.discussions.microsoft.com> wrote in message
news:1F7CC5B5-47B9-41B4-BD89-14263656BC0E@.microsoft.com...
> SQL 2005
> I have a table with a number of nvarchar fields. One field in the midde
> was
> 1000 in length. I ran a query with:
> ALTER TableName
> ALTER ColumnName nvarchar(2000).
> This apeared to increase the field size to 2000 (as seen in Server
> Management Studio), but it will still not store any more than 1000
> characters.
> Have I done it incorrectly.
> There appears to be still space in my record as I can add a field at the
> end
> 2000 long and that works correctly.
> Thanks,
> Steve.
>|||Andrew - many thanks for your reply. That got me thinking and I checked the
(Delplhi) component I am using to link. It had field definitions in it and
I
had forgotten to refresh it. Did that and now all is working correctly.
Thanks,
Steve.
"Andrew J. Kelly" wrote:

> Why do you say it only stores 1000 characters? Is it because what you are
> seeing when you select it? If so then change the max size of the columns
> characters returned in the options menu to support more. What does
> MAX(DATALEN(YourColumn)) say for that column?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Sawlmgsj" <Sawlmgsj@.discussions.microsoft.com> wrote in message
> news:1F7CC5B5-47B9-41B4-BD89-14263656BC0E@.microsoft.com...
>

Change Field Name

I got 30 tables and I want to change one field name ,
Can I use SQL script to do that ?
Thanks a lot .Hi,
look for the procedure sp_rename which is described in the BOL in
detail.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--sql

change datetime to date

I have a datetime field (example: 08/06/2007 9:05:06AM) that I want to change so only the date is in a select statement (so I only get 08/06/2007). How do I write the expression?
Help is appreciated.
Thanksthere is no good way that i know of to do this in ANSI SQL

which database are you using?|||Thanks for posting to my thread. I found a workaround (even though it ain't pretty) but it's working so thanks again for posting.|||what was your workaround?

Change Date Format of Field value

What is the best way of converting datatime field value 29/03/2005 08:58:27 to 29/03/2005.

I only want to remove Time from date and I am using Sql Server 2000.

Thanks

Arvind

Look at the cast and convert functions

I use cast(convert(varchar(10), getdate(), 101) as datetime)

sql

change date format

I have a table with a date field. I need to convert the date so that it
returns in a text format of DDMMYY - no separators.
How do I do this?
I have done it like as follows: Is there a better way of doing it?
Here is what I have:
right(RTrim(convert(char(8),TrnDate,12)),2) +
substring(convert(char(8),TrnDate,12),3,2) +
left(convert(char(8),TrnDate,12),2) as DteVal
"Newbie" <noidea@.nospam.com> wrote in message
news:%23gfQUJ0zEHA.1192@.tk2msftngp13.phx.gbl...
> I have a table with a date field. I need to convert the date so that it
> returns in a text format of DDMMYY - no separators.
> How do I do this?
>
|||On Sat, 20 Nov 2004 21:57:21 -0000, Newbie wrote:

>I have done it like as follows: Is there a better way of doing it?
Hi Newbie,
Have you tried
REPLACE (CONVERT(char(8), TrnDate, 103), '/', '')
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks for the reply. I gave it a go but for the date 17/04/2000 it
returned
170420 instead of
170400
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:55ivp01419rdl6enh9v0lolt1cak6a5r14@.4ax.com...
> On Sat, 20 Nov 2004 21:57:21 -0000, Newbie wrote:
>
> Hi Newbie,
> Have you tried
> REPLACE (CONVERT(char(8), TrnDate, 103), '/', '')
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hugo's solution needs format 3, not 103:
This should work:
REPLACE (CONVERT(char(8), TrnDate, 3), '/', '')
Steve Kass
Drew University
Newbie wrote:

>Thanks for the reply. I gave it a go but for the date 17/04/2000 it
>returned
>170420 instead of
>170400
>
>"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
>news:55ivp01419rdl6enh9v0lolt1cak6a5r14@.4ax.com.. .
>
>
>
|||On Sun, 21 Nov 2004 09:37:38 -0000, Newbie wrote:

>Thanks for the reply. I gave it a go but for the date 17/04/2000 it
>returned
>170420 instead of
>170400
Hi Newbie,
My bad. Halfway through my post, I forgot that you didn't want all four
digits of the year and I autopiloted back to a format that uses the
complete year.
Steve is correct: 103 should have been 3.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks - works a treat!
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:i342q0dcnf10r39nn5m35q4u38cblgtiib@.4ax.com...
> On Sun, 21 Nov 2004 09:37:38 -0000, Newbie wrote:
>
> Hi Newbie,
> My bad. Halfway through my post, I forgot that you didn't want all four
> digits of the year and I autopiloted back to a format that uses the
> complete year.
> Steve is correct: 103 should have been 3.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Change Datafield Length for all Table

in myDatabase, over 50 tables.
Every table got the field 'userid' , Now I need to change the field length
from char(10) to char(15)
How Can I do that ? Any simple and fast way to do ?
Thanks for your kind help
> in myDatabase, over 50 tables.
> Every table got the field 'userid' , Now I need to change the field length
> from char(10) to char(15)
> How Can I do that ? Any simple and fast way to do ?
You could create a cursor, loop though your tables and dynamically
concatenate the ALTER TABLE string and execute it with the EXECUTE
statement. Unfortunately I don't see any other shortcut.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
|||Also you can use undocumented SP sp_Msforeachtable 'your_command [?]', it
executes supplied command on each table in your DB, (it is similar to the
cursor on each table).
"Dejan Sarka" wrote:

> You could create a cursor, loop though your tables and dynamically
> concatenate the ALTER TABLE string and execute it with the EXECUTE
> statement. Unfortunately I don't see any other shortcut.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>
>

Change Datafield Length for all Table

in myDatabase, over 50 tables.
Every table got the field 'userid' , Now I need to change the field length
from char(10) to char(15)
How Can I do that ? Any simple and fast way to do '
Thanks for your kind help> in myDatabase, over 50 tables.
> Every table got the field 'userid' , Now I need to change the field length
> from char(10) to char(15)
> How Can I do that ? Any simple and fast way to do '
You could create a cursor, loop though your tables and dynamically
concatenate the ALTER TABLE string and execute it with the EXECUTE
statement. Unfortunately I don't see any other shortcut.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Also you can use undocumented SP sp_Msforeachtable 'your_command [?]', it
executes supplied command on each table in your DB, (it is similar to the
cursor on each table).
"Dejan Sarka" wrote:
> > in myDatabase, over 50 tables.
> > Every table got the field 'userid' , Now I need to change the field length
> > from char(10) to char(15)
> > How Can I do that ? Any simple and fast way to do '
> You could create a cursor, loop though your tables and dynamically
> concatenate the ALTER TABLE string and execute it with the EXECUTE
> statement. Unfortunately I don't see any other shortcut.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>
>

Change Datafield Length for all Table

in myDatabase, over 50 tables.
Every table got the field 'userid' , Now I need to change the field length
from char(10) to char(15)
How Can I do that ? Any simple and fast way to do '
Thanks for your kind help> in myDatabase, over 50 tables.
> Every table got the field 'userid' , Now I need to change the field length
> from char(10) to char(15)
> How Can I do that ? Any simple and fast way to do '
You could create a cursor, loop though your tables and dynamically
concatenate the ALTER TABLE string and execute it with the EXECUTE
statement. Unfortunately I don't see any other shortcut.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Also you can use undocumented SP sp_Msforeachtable 'your_command [?]', i
t
executes supplied command on each table in your DB, (it is similar to the
cursor on each table).
"Dejan Sarka" wrote:

> You could create a cursor, loop though your tables and dynamically
> concatenate the ALTER TABLE string and execute it with the EXECUTE
> statement. Unfortunately I don't see any other shortcut.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>
>

Change Color of Textbox Depending on Value

He is my question depending on the code field from my database I need to display a different color. The field is a detail in my table with just text displaying the title of the report. I have the actual code value in another column. I then go to the background color expression and am using this code:

=Iif(First(Fields!Code.Value, "CodeDataset") = 3, "Green", Iif(First(Fields!Code.Value, "CodeDataset") = 4, "Blue", "Red" ))

They all work if you pull the report up one at a time, but when selecting multi-values you get the color from the first record on each report page no matter what the second, third or so on values are. The code field does display each correct code. Is this being hard-coded to the first record?

Any Idea's ?|||

if your statement:

=Iif(First(Fields!Code.Value, "CodeDataset") = 3, "Green", Iif(First(Fields!Code.Value, "CodeDataset") = 4, "Blue", "Red" ))

You are evaluting the First Fields value "Literally" if you change it to

=Iif(Fields!Code.Value= 3, "Green", Iif(Fields!Code.Value = 4, "Blue", "Red" )) - red will be the default value I think you will see the proper results.

|||

Great thanks it was simple good I tried it with out the first,but I did still have the "DataSet" in there. What is first called is it a function so I can do some research. Also how do you do is null in an expression, and convert data types for a value? Thanks so much for your help!!!

=Iif(Fields!Days.Value is Null, "AliceBlue", "White")

|||

I tried this code for my returntime field: Do I need to do a data conversion?

=Iif(Fields!ReturnTime.Value <= 0, "AliceBlue", "Transparent")

Build complete -- 0 errors, 0 warnings

[rsRuntimeErrorInExpression] The BackgroundColor expression for the textbox ‘Days’ contains an error: Operator '<=' is not defined for type 'Date' and type 'Integer'.

Preview complete -- 0 errors, 1 warnings

|||

Humm,

I think its the "<=" should work try Fields!ReturnTime.Value < 0 or Fields!ReturnTime.Value = 0 instead.

change a value in a field

I have a table with several fields (like who doesn't)
I would like a quick and easy way to change a field value
If I run a query that says " select fieldA, fieldB, fieldC, fieldD from
tableE where fieldD = '3122007' "
I get a ton of results
What I want to do is if fieldD = 3122007, I want to change that value to
<NULL>
How do I do that?
You can use UPDATE statement like this:
UPDATE tableE
SET fieldD = NULL
WHERE fieldD = 3122007
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||you want it to happen during the query or have the database itself
change the stored value when there's an insert with a specific number?
On Mar 14, 11:31 pm, "Johnfli" <j...@.ivhs.us> wrote:
> I have a table with several fields (like who doesn't)
> I would like a quick and easy way to change a field value
> If I run a query that says " select fieldA, fieldB, fieldC, fieldD from
> tableE where fieldD = '3122007' "
> I get a ton of results
> What I want to do is if fieldD = 3122007, I want to change that value to
> <NULL>
> How do I do that?
|||"Johnfli" <john@.ivhs.us> wrote in message
news:uZEjPCpZHHA.1580@.TK2MSFTNGP05.phx.gbl...
>I have a table with several fields (like who doesn't)
> I would like a quick and easy way to change a field value
> If I run a query that says " select fieldA, fieldB, fieldC, fieldD from
> tableE where fieldD = '3122007' "
> I get a ton of results
> What I want to do is if fieldD = 3122007, I want to change that value to
> <NULL>
>
> How do I do that?
>
If you mean change the field in the table, what Plamen posted is the way.
If you mean in the returned result set, you may want to look at CASE.
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com
|||worked perfectly!!
thank you
"Plamen Ratchev" <Plamen@.SQLStudio.com> wrote in message
news:O%23pVOsqZHHA.1400@.TK2MSFTNGP06.phx.gbl...
> You can use UPDATE statement like this:
> UPDATE tableE
> SET fieldD = NULL
> WHERE fieldD = 3122007
> HTH,
> Plamen Ratchev
> http://www.SQLStudio.com
>

change a value in a field

I have a table with several fields (like who doesn't)
I would like a quick and easy way to change a field value
If I run a query that says " select fieldA, fieldB, fieldC, fieldD from
tableE where fieldD = '3122007' "
I get a ton of results
What I want to do is if fieldD = 3122007, I want to change that value to
<NULL>
How do I do that?You can use UPDATE statement like this:
UPDATE tableE
SET fieldD = NULL
WHERE fieldD = 3122007
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||you want it to happen during the query or have the database itself
change the stored value when there's an insert with a specific number?
On Mar 14, 11:31 pm, "Johnfli" <j...@.ivhs.us> wrote:
> I have a table with several fields (like who doesn't)
> I would like a quick and easy way to change a field value
> If I run a query that says " select fieldA, fieldB, fieldC, fieldD from
> tableE where fieldD = '3122007' "
> I get a ton of results
> What I want to do is if fieldD = 3122007, I want to change that value to
> <NULL>
> How do I do that?|||"Johnfli" <john@.ivhs.us> wrote in message
news:uZEjPCpZHHA.1580@.TK2MSFTNGP05.phx.gbl...
>I have a table with several fields (like who doesn't)
> I would like a quick and easy way to change a field value
> If I run a query that says " select fieldA, fieldB, fieldC, fieldD from
> tableE where fieldD = '3122007' "
> I get a ton of results
> What I want to do is if fieldD = 3122007, I want to change that value to
> <NULL>
>
> How do I do that?
>
If you mean change the field in the table, what Plamen posted is the way.
If you mean in the returned result set, you may want to look at CASE.
--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||worked perfectly!!
thank you
"Plamen Ratchev" <Plamen@.SQLStudio.com> wrote in message
news:O%23pVOsqZHHA.1400@.TK2MSFTNGP06.phx.gbl...
> You can use UPDATE statement like this:
> UPDATE tableE
> SET fieldD = NULL
> WHERE fieldD = 3122007
> HTH,
> Plamen Ratchev
> http://www.SQLStudio.com
>

change a value in a field

I have a table with several fields (like who doesn't)
I would like a quick and easy way to change a field value
If I run a query that says " select fieldA, fieldB, fieldC, fieldD from
tableE where fieldD = '3122007' "
I get a ton of results
What I want to do is if fieldD = 3122007, I want to change that value to
<NULL>
How do I do that?You can use UPDATE statement like this:
UPDATE tableE
SET fieldD = NULL
WHERE fieldD = 3122007
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||you want it to happen during the query or have the database itself
change the stored value when there's an insert with a specific number?
On Mar 14, 11:31 pm, "Johnfli" <j...@.ivhs.us> wrote:
> I have a table with several fields (like who doesn't)
> I would like a quick and easy way to change a field value
> If I run a query that says " select fieldA, fieldB, fieldC, fieldD from
> tableE where fieldD = '3122007' "
> I get a ton of results
> What I want to do is if fieldD = 3122007, I want to change that value to
> <NULL>
> How do I do that?|||"Johnfli" <john@.ivhs.us> wrote in message
news:uZEjPCpZHHA.1580@.TK2MSFTNGP05.phx.gbl...
>I have a table with several fields (like who doesn't)
> I would like a quick and easy way to change a field value
> If I run a query that says " select fieldA, fieldB, fieldC, fieldD from
> tableE where fieldD = '3122007' "
> I get a ton of results
> What I want to do is if fieldD = 3122007, I want to change that value to
> <NULL>
>
> How do I do that?
>
If you mean change the field in the table, what Plamen posted is the way.
If you mean in the returned result set, you may want to look at CASE.
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||worked perfectly!!
thank you
"Plamen Ratchev" <Plamen@.SQLStudio.com> wrote in message
news:O%23pVOsqZHHA.1400@.TK2MSFTNGP06.phx.gbl...
> You can use UPDATE statement like this:
> UPDATE tableE
> SET fieldD = NULL
> WHERE fieldD = 3122007
> HTH,
> Plamen Ratchev
> http://www.SQLStudio.com
>

challenging likes wildcard

Hi all,

I need to search the field containing the word I enter, but this word is bracketed by "{{" and "}}". For example, I would like to search the keyword apple, I need to search by the following sql statement.

select name from table where name likes '%{{apple}}%'

But the case is that that column may contain some space between the bracket and the keyword. i.e. {{ apple }}. How can I write a sql statement to search name from the keyword?

Thanks

Spencer

Hi

You could use

SELECT [name] FROM table WHERE REPLACE ([name] , ' ' , '' ) LIKE '%{{apple}}%'

this will remove all spaces for the prurposes of the search query

|||

Thanks Rod,

But this query will search the keyword ap ple , a pple , app le etc. This is the challenge for me.

Spencer

|||

Hi Spencer,

Can you try the below query

SELECT [name]

FROM "table" (nolock)

WHERE [name] LIKE '%' + LTRIM(RTRIM(REPLACE(REPLACE('{{ apple }}','{',''),'}',''))) + '%'

Eralper

http://www.kodyaz.com

|||

You could do something like below:

declare @.a varchar(30), @.b varchar(30), @.c varchar(30)
set @.a = '{{apple }}' -- '{{$apple$}}'
set @.b = substring(@.a, patindex('%{{%apple%}}%', @.a) + 2, 8000)
set @.c = substring(reverse(@.a), patindex(reverse('%{{%apple%}}%'), reverse(@.a)) + 2, 8000)
if @.a like '%{{%apple%}}%' and substring(@.b, 1, patindex('%apple%}}%', @.b) - 1) not like '%[^ ]%'
and substring(@.c, 1, patindex(reverse('%{{%apple%'), @.c) - 1) not like '%[^ ]%'
print 'Y'
else
print 'N'

You can perform this in a SELECT statement by using a single expression. I am assuming that there could be multiple spaces before and after the keyword. So it is not possible to write a single search pattern for LIKE using the TSQL support. The code checks for any non-space character in addition to keyword match.

|||

Thanks Umachandar

CeWriteRecordProps failling with a empty FILETIME field..

Hi All, Does anyone have already made an application that writes some data into the EDB Pocket database ? I′m trying to insert a record with a empty FILETIME field and I′m getting the Error 87 (INVALID_PARAMETER), I′ve tested with some date in the field and the record is added sucessfull, it seems that the database doesn′t accept anymore empty FILETIME fields? Is it true? I didn't find anything in the docs.

thanx in advance.

The CEPROPVAL is OEM dependent. Have you verified the behavior on other devices too? If that remains the same, I need to investigate further.|||

Hi Thiago,

Are you using CeWriteRecordProps (EDB)? If so, then yes you would get error if you are passing empty FILETIME field. Somehow, this is missed in documentation. We have a bug for that, and I would check the status.

Please pass non-empty FILETIME to get yourself unblocked. Sorry for the pain created.

Thanks,

Laxmi Narsimha Rao ORUGANTI, SQL Server Everywhere, Microsoft Corporation

|||

Hi Laxmi,

Thank you very much for your reply, I′m passing now a non-empty FILETIME and it's working very well, now I'm stuck in another function, a similar problem with the CeSeekDatabaseEx funcion, especially when used with the CEOID, BEGINNING, LAST or CURRENT flags.

In the documentation, MSDN says that isn't necessary to open the database with a sort order when seeking with these flags, but if I do this, get the same ERROR 87 (INVALID_PARAMETER).

The function only works (find a record sucessfully) when I open the database specifing a sort order.

Do you know something about this issue?

In the documentation is:

The ERROR_INVALID_PARAMETER may be returned in the following situations:

x. The database was opened without a specified sort order, and dwSeekType is
a value other than the following:
CEDB_SEEK_CEOID
CEDB_SEEK_BEGINNING
CEDB_SEEK_CURRENT
CEDB_SEEK_END.

All other seek types require the use of a sort order.

link: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/wcedata5/html/wce50lrfCeSeekDatabaseExEDB.asp

Thanx in advance,
Thiago

CeWriteRecordProps failling with a empty FILETIME field..

Hi All, Does anyone have already made an application that writes some data into the EDB Pocket database ? I′m trying to insert a record with a empty FILETIME field and I′m getting the Error 87 (INVALID_PARAMETER), I′ve tested with some date in the field and the record is added sucessfull, it seems that the database doesn′t accept anymore empty FILETIME fields? Is it true? I didn't find anything in the docs.

thanx in advance.

The CEPROPVAL is OEM dependent. Have you verified the behavior on other devices too? If that remains the same, I need to investigate further.|||

Hi Thiago,

Are you using CeWriteRecordProps (EDB)? If so, then yes you would get error if you are passing empty FILETIME field. Somehow, this is missed in documentation. We have a bug for that, and I would check the status.

Please pass non-empty FILETIME to get yourself unblocked. Sorry for the pain created.

Thanks,

Laxmi Narsimha Rao ORUGANTI, SQL Server Everywhere, Microsoft Corporation

|||

Hi Laxmi,

Thank you very much for your reply, I′m passing now a non-empty FILETIME and it's working very well, now I'm stuck in another function, a similar problem with the CeSeekDatabaseEx funcion, especially when used with the CEOID, BEGINNING, LAST or CURRENT flags.

In the documentation, MSDN says that isn't necessary to open the database with a sort order when seeking with these flags, but if I do this, get the same ERROR 87 (INVALID_PARAMETER).

The function only works (find a record sucessfully) when I open the database specifing a sort order.

Do you know something about this issue?

In the documentation is:

The ERROR_INVALID_PARAMETER may be returned in the following situations:

x. The database was opened without a specified sort order, and dwSeekType is
a value other than the following:
CEDB_SEEK_CEOID
CEDB_SEEK_BEGINNING
CEDB_SEEK_CURRENT
CEDB_SEEK_END.

All other seek types require the use of a sort order.

link: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/wcedata5/html/wce50lrfCeSeekDatabaseExEDB.asp

Thanx in advance,
Thiago