clear log: varchar

Showing posts with label varchar. Show all posts

Friday, March 30, 2012

How to conver 24 Hrs. time into 12 Hrs. time

hi
plz help me i have a varchar time like '18:30:15' and i want to convert it into '06:30:15 PM' format
plz help meLook convert statement with it's formats

Monday, March 12, 2012

How to configur a column to be unique value from the Enterprize Ma

Hi, I want one of the column in a table to not have duplicate values(it's
Varchar) but it's not the primary key column which is a auto-generated id
column for the table.
Thanks,
AlphaCreate a unique constraint on the column
http://sqlservercode.blogspot.com/
"Alpha" wrote:
> Hi, I want one of the column in a table to not have duplicate values(it's
> Varchar) but it's not the primary key column which is a auto-generated id
> column for the table.
> Thanks,
> Alpha|||Sorry I wasn't clear on my question. I know I need to set the unique
constraint but just didn't know where is Enterprise Manager is the setting.
I just found it now in the design and right click on the column. thanks for
your help anyway.
"SQL" wrote:
> Create a unique constraint on the column
> http://sqlservercode.blogspot.com/
>
> "Alpha" wrote:
> > Hi, I want one of the column in a table to not have duplicate values(it's
> > Varchar) but it's not the primary key column which is a auto-generated id
> > column for the table.
> >
> > Thanks,
> > Alpha

How to configur a column to be unique value from the Enterprize Ma

Friday, March 9, 2012

how to compare value of two fileds and based on that insert value into third fileds

Hi,

I have a database with table name as test in that i have 6 colums
they are

name varchar (20)
address varchar (20)
position varchar (20)
nametype1 varchar (20)
nametype2 varchar (20)
nameval varchar(20)

now in the nametype1 and nametype2 there are values like
nametype1 nametype2
"AB" "BA"
"BB" "BB"
"AA" "AA"
"BA" "AB"

now depending upon the combination i want to assign value to the thrid
field that is nameval like example below

nametype1 nametype2 nameval
"AB" "BA" 1
"BB" "BB" 2
"AA" "AA" 2
"BA" "AB" 1

please suggest query in sql which i can run to do this .

RegardsHi,

You do not specify what is the logic for generating the values for the
nameval column, but based on your example seems it is 2 when the values are
equal and 1 when they are different.

Based on that assumption, here is a query that will do the update:

update test
set nameval = (case when nametype1 = nametype2 then 2 else 1 end)

Regards,

Plamen Ratchev
http://www.SQLStudio.com|||On Jan 30, 6:47 pm, "Plamen Ratchev" <Pla...@.SQLStudio.comwrote:

Quote:

Originally Posted by

Hi,
>
You do not specify what is the logic for generating the values for the
nameval column, but based on your example seems it is 2 when the values are
equal and 1 when they are different.
>
Based on that assumption, here is a query that will do the update:
>
update test
set nameval = (case when nametype1 = nametype2 then 2 else 1 end)
>
Regards,
>
Plamen Ratchevhttp://www.SQLStudio.com

hi i have an update to the query , please suggest if the table
structure and results are below then what should i run for no of
fileds

name varchar (20)
address varchar (20)
position varchar (20)
nametype1 varchar (20)
nametype2 varchar (20)
nametype3 varchar(20)
nametype4 varchar(20)
nameval varchar(20)
nameval1 varchar(20)
nameval2 varchar(20)
nameval3 varchar(20)

now in the nametype1 and nametype2 there are values like
nametype1 nametype2 nametype3 nametype4
"AB" "BA" "BB" "BB"
"AA" "AA" "BA" "AB"
"AB" "BA" "BB" "BB"
"AA" "AA" "BA" "AB"

now depending upon the combination i want to assign value to the thrid
field that is nameval like example below

nametype1 nametype2 nameval
"AB" "BA" 1
"AA" "AA" 2
"AB" "BA" 1
"AA" "AA" 2

nametype1 nametype3 nameval1
"AB" "BB" 1
"AA" "BA" 1
"AB" "BB" 1
"AA" "BA" 1

nametype1 nametype4 nameval2
"AB" "BB" 1
"AA" "AB" 1
"AB" "BB" 1
"AA" "AB" 1

please suggest query in sql which i can run to do this also i would
like to know is it possible to have some kind of loop which can check
each nametype with other like the combination above please suggest.

Regards|||I am still confused about the logic and the purpose of this, but since it
seems to follow the same pattern (when equal then 2 else 1), here it is (you
just keep repeating the same for the other "nameval" columns):

update test
set nameval = (case when nametype1 = nametype2 then 2 else 1 end),
nameval1 = (case when nametype1 = nametype3 then 2 else 1 end),
nameval2 = (case when nametype1 = nametype4 then 2 else 1 end)

Also, you can create those "nameval" columns as computed columns and then
you do not have to run the update statements. Something like this:

create table test(
name varchar (20),
address varchar (20),
position varchar (20),
nametype1 varchar (20),
nametype2 varchar (20),
nametype3 varchar(20),
nametype4 varchar(20),
nameval as (case when nametype1 = nametype2 then 2 else 1 end),
nameval1 as (case when nametype1 = nametype3 then 2 else 1 end),
nameval2 as (case when nametype1 = nametype4 then 2 else 1 end))

insert into test (nametype1, nametype2, nametype3, nametype4) values ('AA',
'AA', 'AB', 'BA')

select * from test

drop table test

Perhaps the table should be normalized too, but since no requirements are
given I do not want to guess...

HTH,

Plamen Ratchev
http://www.SQLStudio.com|||On Jan 31, 11:42 am, "Plamen Ratchev" <Pla...@.SQLStudio.comwrote:

Quote:

Originally Posted by

I am still confused about the logic and the purpose of this, but since it
seems to follow the same pattern (when equal then 2 else 1), here it is (you
just keep repeating the same for the other "nameval" columns):
>
update test
set nameval = (case when nametype1 = nametype2 then 2 else 1 end),
nameval1 = (case when nametype1 = nametype3 then 2 else 1 end),
nameval2 = (case when nametype1 = nametype4 then 2 else 1 end)
>
Also, you can create those "nameval" columns as computed columns and then
you do not have to run the update statements. Something like this:
>
create table test(
name varchar (20),
address varchar (20),
position varchar (20),
nametype1 varchar (20),
nametype2 varchar (20),
nametype3 varchar(20),
nametype4 varchar(20),
nameval as (case when nametype1 = nametype2 then 2 else 1 end),
nameval1 as (case when nametype1 = nametype3 then 2 else 1 end),
nameval2 as (case when nametype1 = nametype4 then 2 else 1 end))
>
insert into test (nametype1, nametype2, nametype3, nametype4) values ('AA',
'AA', 'AB', 'BA')
>
select * from test
>
drop table test
>
Perhaps the table should be normalized too, but since no requirements are
given I do not want to guess...
>
HTH,
>
Plamen Ratchevhttp://www.SQLStudio.com

is it possible to run the query in existing table , please suggest how
and also if there more than 20 nametypes is there query which can loop
through all the name types and do the job.

please suggest any reference too if any.

Regards|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

Even your narrative is wrong. Did you know that double quotes are not
used for string values in SQL? Have you heard of ISO-11179 rules for
data element names? Absurd things like "name_type_1" look like a
repeating group in violationof 1NF which will lead to some really
horrible kludges and a loss of data integrity.

Please try again and pretend that you have to work from these specs
without any prior knowledge -- we do SQL, not mind-reading :)|||Yes, the query will update an existing table, just keep adding lines for
each column, like this:

As for you reference to do the update in a loop, then this is doable via
dynamic SQL. Here is a quick sketch of how it may look (by adjusting the
number 10 you will get different number of columns, beware of the 4000
character limit on the SQL string):

DECLARE @.sql nvarchar(4000),
@.count int,
@.numcolumns int

SELECT @.sql = 'update test set ', @.count = 1, @.numcolumns = 10

WHILE @.count <= @.numcolumns
BEGIN
IF @.count = 1
SELECT @.sql = @.sql + 'nameval = (case when nametype1 = nametype' +
CAST(@.count + 1 as nvarchar) + ' then 2 else 1 end)'
ELSE
SELECT @.sql = @.sql + ', nameval' + CAST(@.count - 1 as nvarchar) + ' =
(case when nametype1 = nametype' + CAST(@.count + 1 as nvarchar) + ' then 2
else 1 end)'

SELECT @.count = @.count + 1
END

EXEC(@.sql)

Erland Sommarskog has an excellent guide on dynamic SQL at
http://www.sommarskog.se/dynamic_sql.html. I would recommend reading it
before jumping into using dynamic SQL. Also, please read the comment from
Celko, he is correct that without posting DLL and specifications it is very
difficult to get good answers.

Regards,

Plamen Ratchev
http://www.SQLStudio.com|||Tradeorganizer wrote:

Quote:

Originally Posted by

I have a database with table name as test in that i have 6 colums
they are
>
name varchar (20)
address varchar (20)
position varchar (20)
nametype1 varchar (20)
nametype2 varchar (20)
nameval varchar(20)
>
now in the nametype1 and nametype2 there are values like
nametype1 nametype2
"AB" "BA"
"BB" "BB"
"AA" "AA"
"BA" "AB"
>
now depending upon the combination i want to assign value to the thrid
field that is nameval like example below
>
nametype1 nametype2 nameval
"AB" "BA" 1
"BB" "BB" 2
"AA" "AA" 2
"BA" "AB" 1
>
please suggest query in sql which i can run to do this .

A couple of things that others have touched on, but not made
quite this explicit:

/Why/ should nametype1 = "AB" and nametype2 = "BA" lead to
nameval = 1? What is the general rule that you want to apply?

Why do you have more than 20 nametypes? Please give serious
consideration to splitting this table into two tables, e.g.

[table1]
person_id, name, address, position
1, 'John Doe', '123 Cherry Lane', 'Regional Manager'
2, 'Thomas Atkins', '987 Easy Street', 'President'

[table2]
person_id, nameindex, nametype, nameval
1, 1, 'AB', 1
1, 2, 'BA', 1
2, 1, 'BB', 2
2, 2, 'BB', 2|||Thanks for great help , yes its working for me.

Thanks to all for taking time to explain me.

Regards

Ed Murphy wrote:

Quote:

Originally Posted by

Tradeorganizer wrote:
>

Quote:

Originally Posted by

I have a database with table name as test in that i have 6 colums
they are

name varchar (20)
address varchar (20)
position varchar (20)
nametype1 varchar (20)
nametype2 varchar (20)
nameval varchar(20)

now in the nametype1 and nametype2 there are values like
nametype1 nametype2
"AB" "BA"
"BB" "BB"
"AA" "AA"
"BA" "AB"

now depending upon the combination i want to assign value to the thrid
field that is nameval like example below

nametype1 nametype2 nameval
"AB" "BA" 1
"BB" "BB" 2
"AA" "AA" 2
"BA" "AB" 1

please suggest query in sql which i can run to do this .

>
A couple of things that others have touched on, but not made
quite this explicit:
>
/Why/ should nametype1 = "AB" and nametype2 = "BA" lead to
nameval = 1? What is the general rule that you want to apply?
>
Why do you have more than 20 nametypes? Please give serious
consideration to splitting this table into two tables, e.g.
>
[table1]
person_id, name, address, position
1, 'John Doe', '123 Cherry Lane', 'Regional Manager'
2, 'Thomas Atkins', '987 Easy Street', 'President'
>
[table2]
person_id, nameindex, nametype, nameval
1, 1, 'AB', 1
1, 2, 'BA', 1
2, 1, 'BB', 2
2, 2, 'BB', 2

how to compare value of two fileds and based on that insert value into third fileds

Hi,
I have a database with table name as test in that i have 6 colums
they are
name varchar (20)
address varchar (20)
position varchar (20)
nametype1 varchar (20)
nametype2 varchar (20)
nameval varchar(20)
now in the nametype1 and nametype2 there are values like
nametype1 nametype2
"AB" "BA"
"BB" "BB"
"AA" "AA"
"BA" "AB"
now depending upon the combination i want to assign value to the thrid
field that is nameval like example below
nametype1 nametype2 nameval
"AB" "BA" 1
"BB" "BB" 2
"AA" "AA" 2
"BA" "AB" 1
please suggest query in sql which i can run to do this .
RegardsYou can use a computed column. Check this script:
CREATE TABLE dbo.t1
(nametype1 char(2),
nametype2 char(2),
nameval AS
CASE
WHEN nametype1 = nametype2 THEN 2
WHEN nametype1 = REVERSE(nametype2) THEN 1
ELSE 0 -- error?
END)
GO
INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('AB','BA')
INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('BB','BB')
INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('AA','AA')
INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('BA','AB')
SELECT *
FROM dbo.t1
--
Dejan Sarka
http://www.solidqualitylearning.com/blogs/
"Tradeorganizer" <tradeorganizer@.gmail.com> wrote in message
news:1170146292.237194.199580@.q2g2000cwa.googlegroups.com...
> Hi,
> I have a database with table name as test in that i have 6 colums
> they are
> name varchar (20)
> address varchar (20)
> position varchar (20)
> nametype1 varchar (20)
> nametype2 varchar (20)
> nameval varchar(20)
> now in the nametype1 and nametype2 there are values like
> nametype1 nametype2
> "AB" "BA"
> "BB" "BB"
> "AA" "AA"
> "BA" "AB"
> now depending upon the combination i want to assign value to the thrid
> field that is nameval like example below
> nametype1 nametype2 nameval
> "AB" "BA" 1
> "BB" "BB" 2
> "AA" "AA" 2
> "BA" "AB" 1
> please suggest query in sql which i can run to do this .
> Regards
>|||On Jan 30, 3:52 pm, "Dejan Sarka"
<dejan_please_reply_to_newsgroups.sa...@.avtenta.si> wrote:
> You can use a computed column. Check this script:
> CREATE TABLE dbo.t1
> (nametype1 char(2),
> nametype2 char(2),
> nameval AS
> CASE
> WHEN nametype1 = nametype2 THEN 2
> WHEN nametype1 = REVERSE(nametype2) THEN 1
> ELSE 0 -- error?
> END)
> GO
> INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('AB','BA')
> INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('BB','BB')
> INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('AA','AA')
> INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('BA','AB')
> SELECT *
> FROM dbo.t1
> --
> Dejan Sarkahttp://www.solidqualitylearning.com/blogs/
> "Tradeorganizer" <tradeorgani...@.gmail.com> wrote in message
> news:1170146292.237194.199580@.q2g2000cwa.googlegroups.com...
> > Hi,
> > I have a database with table name as test in that i have 6 colums
> > they are
> > name varchar (20)
> > address varchar (20)
> > position varchar (20)
> > nametype1 varchar (20)
> > nametype2 varchar (20)
> > nameval varchar(20)
> > now in the nametype1 and nametype2 there are values like
> > nametype1 nametype2
> > "AB" "BA"
> > "BB" "BB"
> > "AA" "AA"
> > "BA" "AB"
> > now depending upon the combination i want to assign value to the thrid
> > field that is nameval like example below
> > nametype1 nametype2 nameval
> > "AB" "BA" 1
> > "BB" "BB" 2
> > "AA" "AA" 2
> > "BA" "AB" 1
> > please suggest query in sql which i can run to do this .
> > Regards
instead of creatating in new table can i update the value in
existing table please suggest.|||UPDATE t1 SET nameval =CASE WHEN nametype1 = nametype2 THEN 1
WHEN nametype1 = REVERSE(nametype2) THEN 2
END
Regards
Amish Shah
http://shahamishm.tripod.com
On Jan 30, 4:16 pm, "Tradeorganizer" <tradeorgani...@.gmail.com> wrote:
> On Jan 30, 3:52 pm, "Dejan Sarka"
>
>
> <dejan_please_reply_to_newsgroups.sa...@.avtenta.si> wrote:
> > You can use a computed column. Check this script:
> > CREATE TABLE dbo.t1
> > (nametype1 char(2),
> > nametype2 char(2),
> > nameval AS
> > CASE
> > WHEN nametype1 = nametype2 THEN 2
> > WHEN nametype1 = REVERSE(nametype2) THEN 1
> > ELSE 0 -- error?
> > END)
> > GO
> > INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('AB','BA')
> > INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('BB','BB')
> > INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('AA','AA')
> > INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('BA','AB')
> > SELECT *
> > FROM dbo.t1
> > --
> > Dejan Sarkahttp://www.solidqualitylearning.com/blogs/
> > "Tradeorganizer" <tradeorgani...@.gmail.com> wrote in message
> >news:1170146292.237194.199580@.q2g2000cwa.googlegroups.com...
> > > Hi,
> > > I have a database with table name as test in that i have 6 colums
> > > they are
> > > name varchar (20)
> > > address varchar (20)
> > > position varchar (20)
> > > nametype1 varchar (20)
> > > nametype2 varchar (20)
> > > nameval varchar(20)
> > > now in the nametype1 and nametype2 there are values like
> > > nametype1 nametype2
> > > "AB" "BA"
> > > "BB" "BB"
> > > "AA" "AA"
> > > "BA" "AB"
> > > now depending upon the combination i want to assign value to the thrid
> > > field that is nameval like example below
> > > nametype1 nametype2 nameval
> > > "AB" "BA" 1
> > > "BB" "BB" 2
> > > "AA" "AA" 2
> > > "BA" "AB" 1
> > > please suggest query in sql which i can run to do this .
> > > Regards
> instead of creatating in new table can i update the value in
> existing table please suggest.- Hide quoted text -
> - Show quoted text -|||On Jan 30, 5:33 pm, "amish" <shahami...@.gmail.com> wrote:
> UPDATE t1 SET nameval => CASE WHEN nametype1 = nametype2 THEN 1
> WHEN nametype1 = REVERSE(nametype2) THEN 2
> END
> Regards
> Amish Shahhttp://shahamishm.tripod.com
> On Jan 30, 4:16 pm, "Tradeorganizer" <tradeorgani...@.gmail.com> wrote:
> > On Jan 30, 3:52 pm, "Dejan Sarka"
> > <dejan_please_reply_to_newsgroups.sa...@.avtenta.si> wrote:
> > > You can use a computed column. Check this script:
> > > CREATE TABLE dbo.t1
> > > (nametype1 char(2),
> > > nametype2 char(2),
> > > nameval AS
> > > CASE
> > > WHEN nametype1 = nametype2 THEN 2
> > > WHEN nametype1 = REVERSE(nametype2) THEN 1
> > > ELSE 0 -- error?
> > > END)
> > > GO
> > > INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('AB','BA')
> > > INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('BB','BB')
> > > INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('AA','AA')
> > > INSERT INTO dbo.t1(nametype1, nametype2) VALUES ('BA','AB')
> > > SELECT *
> > > FROM dbo.t1
> > > --
> > > Dejan Sarkahttp://www.solidqualitylearning.com/blogs/
> > > "Tradeorganizer" <tradeorgani...@.gmail.com> wrote in message
> > >news:1170146292.237194.199580@.q2g2000cwa.googlegroups.com...
> > > > Hi,
> > > > I have a database with table name as test in that i have 6 colums
> > > > they are
> > > > name varchar (20)
> > > > address varchar (20)
> > > > position varchar (20)
> > > > nametype1 varchar (20)
> > > > nametype2 varchar (20)
> > > > nameval varchar(20)
> > > > now in the nametype1 and nametype2 there are values like
> > > > nametype1 nametype2
> > > > "AB" "BA"
> > > > "BB" "BB"
> > > > "AA" "AA"
> > > > "BA" "AB"
> > > > now depending upon the combination i want to assign value to the thrid
> > > > field that is nameval like example below
> > > > nametype1 nametype2 nameval
> > > > "AB" "BA" 1
> > > > "BB" "BB" 2
> > > > "AA" "AA" 2
> > > > "BA" "AB" 1
> > > > please suggest query in sql which i can run to do this .
> > > > Regards
> > instead of creatating in new table can i update the value in
> > existing table please suggest.- Hide quoted text -
> > - Show quoted text -
hi i have an update to the query , please suggest if the table
structure and results are below then what should i run for no of
fileds
name varchar (20)
address varchar (20)
position varchar (20)
nametype1 varchar (20)
nametype2 varchar (20)
nametype3 varchar(20)
nametype4 varchar(20)
nameval varchar(20)
nameval1 varchar(20)
nameval2 varchar(20)
nameval3 varchar(20)
now in the nametype1 and nametype2 there are values like
nametype1 nametype2 nametype3 nametype4
"AB" "BA" "BB" "BB"
"AA" "AA" "BA" "AB"
"AB" "BA" "BB" "BB"
"AA" "AA" "BA" "AB"
now depending upon the combination i want to assign value to the thrid
field that is nameval like example below
nametype1 nametype2 nameval
"AB" "BA" 1
"AA" "AA" 2
"AB" "BA" 1
"AA" "AA" 2
nametype1 nametype3 nameval1
"AB" "BB" 1
"AA" "BA" 1
"AB" "BB" 1
"AA" "BA" 1
nametype1 nametype4 nameval2
"AB" "BB" 1
"AA" "AB" 1
"AB" "BB" 1
"AA" "AB" 1
please suggest query in sql which i can run to do this also i would
like to know is it possible to have some kind of loop which can check
each nametype with other like the combination above please suggest.
Regards

Wednesday, March 7, 2012

How to compare strings for equality in Transact SQL

I just learned that if a VARCHAR column contains 'ABC ' (three letters
and a space) then
SELECT * FROM ATABLE WHERE THECOLUMN = 'ABC'
will return the record since SQL Server incorrectly pads the shorter
value with spaces to the length of the longer before comparing them. Is
there a way to compare strings for equality that works correctly?
.Bill.trim?
SELECT * FROM ATABLE WHERE trim(THECOLUMN) = 'ABC'
"Bill" <no@.no.com> wrote in message
news:uhKlUmxMGHA.3728@.tk2msftngp13.phx.gbl...
>I just learned that if a VARCHAR column contains 'ABC ' (three letters
> and a space) then
> SELECT * FROM ATABLE WHERE THECOLUMN = 'ABC'
> will return the record since SQL Server incorrectly pads the shorter
> value with spaces to the length of the longer before comparing them. Is
> there a way to compare strings for equality that works correctly?
> --
> .Bill.|||THECOLUMN like 'ABC'
or
THECOLUMN+'$' = 'ABC'+'$'
"Bill" <no@.no.com> wrote in message
news:uhKlUmxMGHA.3728@.tk2msftngp13.phx.gbl...
>I just learned that if a VARCHAR column contains 'ABC ' (three letters
> and a space) then
> SELECT * FROM ATABLE WHERE THECOLUMN = 'ABC'
> will return the record since SQL Server incorrectly pads the shorter
> value with spaces to the length of the longer before comparing them. Is
> there a way to compare strings for equality that works correctly?
> --
> .Bill.|||In SQL Server use RTRIM
Jack Vamvas
________________________________________
__________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
New article by Jack Vamvas - Improper Use of indexes on MS SQL: Server
2000 - www.ciquery.com/articles/useofindexes.asp
"Bill" <no@.no.com> wrote in message
news:uhKlUmxMGHA.3728@.tk2msftngp13.phx.gbl...
> I just learned that if a VARCHAR column contains 'ABC ' (three letters
> and a space) then
> SELECT * FROM ATABLE WHERE THECOLUMN = 'ABC'
> will return the record since SQL Server incorrectly pads the shorter
> value with spaces to the length of the longer before comparing them. Is
> there a way to compare strings for equality that works correctly?
> --
> .Bill.|||Grant wrote:

> trim?
> SELECT * FROM ATABLE WHERE trim(THECOLUMN) = 'ABC'
I must be missing something. Using trim gets the same incorrect result
as not using trim. The values 'ABC ' and 'ABC' are NOT equal. If I have
a table with a VARCHAR column that contains two rows with the values
'ABC ' and 'ABC', how do I write a SELECT statement that will return
the row that contains 'ABC' but not the row that contains 'ABC '?
.Bill.|||I think this is the problem he is having:
if 'ABC ' = 'ABC' print 'True' else print 'False'
Result: True
I am assuming he wants this condition to be False. In that base, he actually
doesn't want to trim the spaces.
"Jack Vamvas" <DELETE_BEFORE_REPLY_jack@.ciquery.com> wrote in message
news:dt2c11$m5j$1@.nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...
> In SQL Server use RTRIM
> --
> Jack Vamvas
> ________________________________________
__________________________
> Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
> New article by Jack Vamvas - Improper Use of indexes on MS SQL: Server
> 2000 - www.ciquery.com/articles/useofindexes.asp
> "Bill" <no@.no.com> wrote in message
> news:uhKlUmxMGHA.3728@.tk2msftngp13.phx.gbl...
>|||Grant and Jack, I think the OP wants to consider 'ABC' and 'ABC ' to be
*different* (in most configurations, SQL Server considers them equal because
the trailing spaces on varchar columns are ignored).
So, using RTRIM() does not help because it yields the same result the OP is
already getting.
"Jack Vamvas" <DELETE_BEFORE_REPLY_jack@.ciquery.com> wrote in message
news:dt2c11$m5j$1@.nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...
> In SQL Server use RTRIM
> --
> Jack Vamvas
> ________________________________________
__________________________
> Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
> New article by Jack Vamvas - Improper Use of indexes on MS SQL: Server
> 2000 - www.ciquery.com/articles/useofindexes.asp
> "Bill" <no@.no.com> wrote in message
> news:uhKlUmxMGHA.3728@.tk2msftngp13.phx.gbl...
>|||T-SQL has RTRIM() and LTRIM() but no TRIM().
For other info, please see my reply to Jack.
"Grant" <email@.nowhere.com> wrote in message
news:ejQpurxMGHA.3144@.TK2MSFTNGP11.phx.gbl...
> trim?
> SELECT * FROM ATABLE WHERE trim(THECOLUMN) = 'ABC'|||Aaron Bertrand [SQL Server MVP] wrote:

> Grant and Jack, I think the OP wants to consider 'ABC' and 'ABC ' to
> be different
That is exactly what I want.
.Bill.|||> will return the record since SQL Server incorrectly pads the shorter
> value with spaces to the length of the longer before comparing them.
Actually, SQL Server is ignoring the trailing spaces, but the result is the
same.
There are a few ways to solve this, JT's solution is pretty simple to
implement...

Friday, March 30, 2012

How to conver 24 Hrs. time into 12 Hrs. time

Monday, March 12, 2012

How to configur a column to be unique value from the Enterprize Ma

How to configur a column to be unique value from the Enterprize Ma

Friday, March 9, 2012

how to compare value of two fileds and based on that insert value into third fileds

how to compare value of two fileds and based on that insert value into third fileds

how to compare value of two fileds and based on that insert value into third fileds

how to compare value of two fileds and based on that insert value into third fileds

Wednesday, March 7, 2012

How to compare strings for equality in Transact SQL

clear log

Blog Archive

About Me