Replacement for my LIKE Clause

Dear all,
I need some help from all my Transact SQL Guru friends out there..
Here is the scenario in its most simplified form.. ..
I have two tables.. A(Lookup table) and B(Transaction Table)
TableA Fields
EmployeeLocationID
EmployeeLocation (This could have values say
"B","BO","BOM","C","CA","CALC") etc...
TableB Fields
EmployeeID
EmployeeName......
EmployeeLocationID (will have null initially when rows are populated
first time)
EmployeeLocation (This could have values
"BA123","BOMBAY","BOTS123","BRACK"... etc)
I hope you get where I am leading this to, from my examples..
Requirement is to populate the EmployeeLocationID in Table B with
EmployeeLocationID from TableA by matching the field EmployeeLocation
in both tables.Please note that table B's EmployeeLocation could be A's
EmployeeLocation + some additionalcodes like "123","RACK" etc in the
above example...
Therefore, this is what I had wrote initially..
update B
set B.EmployeeLocationID =A.EmployeeLocationID
>From B inner join A on B.EmployeeLocation Like A.EmployeeLocation +
'%'
where B.EmployeeLocationID is null
This works fine alright.. However the trouble is that it doesn't cater
to the complete requirement...
For example the row in Table B with EmployeeLocation as "BOMBAY" will
get the EmployeeLocationID for "B" or "BO" and not "BOM" because they
are earlier rows in table A while comparing..The requirement is that we
should get the EmployeeLocationID of "BOM" in this case... That is,
the comparison should be done first for the maximum "maximum no of
characters" match, then for the next "no of characters" match, then for
the next "no of characters"match... etc...
Therefore this is the expected match for my examples based on
requirement..
"BA123" from Table B should be mapped to EmployeeLocationID for "B" of
Table A
"BOMBAY" from Table B should be mapped to EmployeeLocationID for "BOM"
of Table A
"BOTS123" from Table B should be mapped to EmployeeLocationID for "BO"
of Table A
"BRACK" from Table B should be mapped to EmployeeLocationID for "B" of
Table A
Can someone please help me with my query, or atleast direct me to the
right material so that I can take care of this requirement..
Looking forward to hearing from someone ASAP.. Please help..
Best regards,
VM...Interesting. Maybe this will give you and angle to try.
UPDATE B
SET EmployeeLocationID = (SELECT TOP 1 A.EmployeeLocationID
FROM A
WHERE B.EmployeeLocation LIKE A.EmployeeLocation + '%'
ORDER BY LEN(A.EmployeeLocation) DESC)
WHERE B.EmployeeLocationID IS NULL
Roy Harvey
Beacon Falls, CT
On 27 Dec 2006 15:24:44 -0800, varkey.mathew@.wipro.com wrote:
>Dear all,
>I need some help from all my Transact SQL Guru friends out there..
>Here is the scenario in its most simplified form.. ..
>I have two tables.. A(Lookup table) and B(Transaction Table)
>TableA Fields
> EmployeeLocationID
> EmployeeLocation (This could have values say
>"B","BO","BOM","C","CA","CALC") etc...
>
>TableB Fields
> EmployeeID
> EmployeeName......
> EmployeeLocationID (will have null initially when rows are populated
>first time)
> EmployeeLocation (This could have values
>"BA123","BOMBAY","BOTS123","BRACK"... etc)
>I hope you get where I am leading this to, from my examples..
>Requirement is to populate the EmployeeLocationID in Table B with
>EmployeeLocationID from TableA by matching the field EmployeeLocation
>in both tables.Please note that table B's EmployeeLocation could be A's
>EmployeeLocation + some additionalcodes like "123","RACK" etc in the
>above example...
>Therefore, this is what I had wrote initially..
>update B
>set B.EmployeeLocationID =A.EmployeeLocationID
>>From B inner join A on B.EmployeeLocation Like A.EmployeeLocation +
>'%'
>where B.EmployeeLocationID is null
>This works fine alright.. However the trouble is that it doesn't cater
>to the complete requirement...
>For example the row in Table B with EmployeeLocation as "BOMBAY" will
>get the EmployeeLocationID for "B" or "BO" and not "BOM" because they
>are earlier rows in table A while comparing..The requirement is that we
>should get the EmployeeLocationID of "BOM" in this case... That is,
>the comparison should be done first for the maximum "maximum no of
>characters" match, then for the next "no of characters" match, then for
>the next "no of characters"match... etc...
>Therefore this is the expected match for my examples based on
>requirement..
>"BA123" from Table B should be mapped to EmployeeLocationID for "B" of
>Table A
>"BOMBAY" from Table B should be mapped to EmployeeLocationID for "BOM"
>of Table A
>"BOTS123" from Table B should be mapped to EmployeeLocationID for "BO"
>of Table A
>"BRACK" from Table B should be mapped to EmployeeLocationID for "B" of
>Table A
>
>Can someone please help me with my query, or atleast direct me to the
>right material so that I can take care of this requirement..
>
>Looking forward to hearing from someone ASAP.. Please help..
>Best regards,
>VM...|||Why did you fail to post DDL, screw up the syntax and violate ISO-11179
naming rules? Probably because you also confuse fields and columns.
Let's start by cleaning up you code, so it looks like SQL.
SQL uses single quotes for strings. A data element can be a location or
an identifier, never both. A transaction is some kind of transaction.
Etc. You need a data modeling course. Your sample data failed to give
values of the improperly named 'EmployeeLocationID' - I hope to
ghod you are not using IDENTITY and thinking that it is a key!!
Don't you know about SAN and other industry standard address numbers?
>> A(Lookup table) and B(Transaction Table) <<
Why did you avoid clear names?
CREATE TABLE LocationCodes
(loc_prefix VARCHAR(5) NOT NULL PRIMARY KEY,
loc_code INTEGER NOT NULL); -- industry SAN '
-- put wildcards in the table for indexing
INSERT INTO LocationCodes VALUES ('B%', 100);
INSERT INTO LocationCodes VALUES ('BO%', 101);
INSERT INTO LocationCodes VALUES ('BOM%', 102);
Etc.
Can two prefixes belong to the same SAN? No specs given.
Without a key in that vague transactions table, you do not have a
proper table at all. I had to make up one. Why do you have employee
id and not find the employee name via a join to the Personnel table?
Isn't the idea of RDBMS to get rid of redudant data?
CREATE TABLE FoobarTrans
(foobar_trans_nbr INTEGER NOT NULL PRIMARY KEY,
-- CHECK (<<needs validation rule here>>),
emp_id INTEGER NOT NULL
REFERENCES Personnel(emp_id)
ON UPDATE CASCADE,
loc_code INTEGER NOT NULL
REFERENCES LocationCodes(loc_code)
ON UPDATE CASCADE,
Etc.);
The prefix should have been used when you inserted the initial row (NOT
field!!!) into the table. Because you are confusing fields and
columns, files and tables, you are thinking in procedural *steps* with
updates just like a punch card file, not in sets like an SQL
programmer.
>> I hope you get where I am leading this to, from my examples.. <<
No. Clear specs would have been nice, along with real DDL.
Here is a skeleton of a proc for this. You can put Roy's SELECT TOP
in the VALUES list, but if you have SQL-2005, try this little untested
statement:
INSERT INTO FoobarTrans (foobar_trans_nbr, emp_id, ..)
VALUES (@.my_foobar_trans_nbr, @.my_emp_id,
(WITH (SELECT L1.loc_code, LEN(L1.loc_prefix)
FROM LocationCodes AS L1
WHERE L1.loc_prefix LIKE @.my_loc_prefix)
AS M(loc_code, fit)
SELECT loc_code
FROM M AS M1
WHERE M1.fit
= (SELECT MAX(M2.fit) FROM M AS M2)),
Etc.);
You will need error handling code for prefixes that do not match.|||Roy,
Thanks a tonne for your prompt and timely response... I could modify my
script on the lines of your code and it worked (smile)..
Celko,
Thanks to you as well, for your valuable suggestions... And I can
understand your outburst... I just jotted down something(without even
proof reading it) because the intend was to get the question out
yesterday, to hopefully get a response by today... Clear names were not
used, Redundancy was there etc... because it was a cooked up scenario,
but my requirement was very like the one I had outlined ...
I really appreciate the time you have taken to progressively take apart
my question... But as long as you understood the original intend on
where I was stuck and I got a solution to my problem, Believe me I am
happy...
I will remember that I might upset Guru's like you with my questions,
in future, and be more careful with its structure and wording...
Thanks once again...
VM
--CELKO-- wrote:
> Why did you fail to post DDL, screw up the syntax and violate ISO-11179
> naming rules? Probably because you also confuse fields and columns.
> Let's start by cleaning up you code, so it looks like SQL.
> SQL uses single quotes for strings. A data element can be a location or
> an identifier, never both. A transaction is some kind of transaction.
> Etc. You need a data modeling course. Your sample data failed to give
> values of the improperly named 'EmployeeLocationID' - I hope to
> ghod you are not using IDENTITY and thinking that it is a key!!
> Don't you know about SAN and other industry standard address numbers?
>
> >> A(Lookup table) and B(Transaction Table) <<
> Why did you avoid clear names?
> CREATE TABLE LocationCodes
> (loc_prefix VARCHAR(5) NOT NULL PRIMARY KEY,
> loc_code INTEGER NOT NULL); -- industry SAN '
> -- put wildcards in the table for indexing
> INSERT INTO LocationCodes VALUES ('B%', 100);
> INSERT INTO LocationCodes VALUES ('BO%', 101);
> INSERT INTO LocationCodes VALUES ('BOM%', 102);
> Etc.
> Can two prefixes belong to the same SAN? No specs given.
> Without a key in that vague transactions table, you do not have a
> proper table at all. I had to make up one. Why do you have employee
> id and not find the employee name via a join to the Personnel table?
> Isn't the idea of RDBMS to get rid of redudant data?
> CREATE TABLE FoobarTrans
> (foobar_trans_nbr INTEGER NOT NULL PRIMARY KEY,
> -- CHECK (<<needs validation rule here>>),
> emp_id INTEGER NOT NULL
> REFERENCES Personnel(emp_id)
> ON UPDATE CASCADE,
> loc_code INTEGER NOT NULL
> REFERENCES LocationCodes(loc_code)
> ON UPDATE CASCADE,
> Etc.);
> The prefix should have been used when you inserted the initial row (NOT
> field!!!) into the table. Because you are confusing fields and
> columns, files and tables, you are thinking in procedural *steps* with
> updates just like a punch card file, not in sets like an SQL
> programmer.
> >> I hope you get where I am leading this to, from my examples.. <<
> No. Clear specs would have been nice, along with real DDL.
> Here is a skeleton of a proc for this. You can put Roy's SELECT TOP
> in the VALUES list, but if you have SQL-2005, try this little untested
> statement:
> INSERT INTO FoobarTrans (foobar_trans_nbr, emp_id, ..)
> VALUES (@.my_foobar_trans_nbr, @.my_emp_id,
> (WITH (SELECT L1.loc_code, LEN(L1.loc_prefix)
> FROM LocationCodes AS L1
> WHERE L1.loc_prefix LIKE @.my_loc_prefix)
> AS M(loc_code, fit)
> SELECT loc_code
> FROM M AS M1
> WHERE M1.fit
> = (SELECT MAX(M2.fit) FROM M AS M2)),
> Etc.);
> You will need error handling code for prefixes that do not match.

Replacement for my LIKE Clause

Dear all,

I need some help from all my Transact SQL Guru friends out there..

Here is the scenario in its most simplified form.. ..

I have two tables.. A(Lookup table) and B(Transaction Table)

TableA Fields
EmployeeLocationID
EmployeeLocation (This could have values say
"B","BO","BOM","C","CA","CALC") etc...

TableB Fields
EmployeeID
EmployeeName......
EmployeeLocationID (will have null initially when rows are populated
first time)
EmployeeLocation (This could have values
"BA123","BOMBAY","BOTS123","BRACK"... etc)

I hope you get where I am leading this to, from my examples..
Requirement is to populate the EmployeeLocationID in Table B with
EmployeeLocationID from TableA by matching the field EmployeeLocation
in both tables.Please note that table B's EmployeeLocation could be A's
EmployeeLocation + some additionalcodes like "123","RACK" etc in the
above example...

Therefore, this is what I had wrote initially..

update B
set B.EmployeeLocationID =A.EmployeeLocationID

Quote:

Originally Posted by

>From B inner join A on B.EmployeeLocation Like A.EmployeeLocation +

'%'
where B.EmployeeLocationID is null

This works fine alright.. However the trouble is that it doesn't cater
to the complete requirement...

For example the row in Table B with EmployeeLocation as "BOMBAY" will
get the EmployeeLocationID for "B" or "BO" and not "BOM" because they
are earlier rows in table A while comparing..The requirement is that we
should get the EmployeeLocationID of "BOM" in this case... That is,
the comparison should be done first for the maximum "maximum no of
characters" match, then for the next "no of characters" match, then for
the next "no of characters"match... etc...

Therefore this is the expected match for my examples based on
requirement..

"BA123" from Table B should be mapped to EmployeeLocationID for "B" of
Table A
"BOMBAY" from Table B should be mapped to EmployeeLocationID for "BOM"
of Table A
"BOTS123" from Table B should be mapped to EmployeeLocationID for "BO"
of Table A
"BRACK" from Table B should be mapped to EmployeeLocationID for "B" of
Table A

Can someone please help me with my query, or atleast direct me to the
right material so that I can take care of this requirement..

Looking forward to hearing from someone ASAP.. Please help..

Best regards,

VM...Interesting. Maybe this will give you and angle to try.

UPDATE B
SET EmployeeLocationID =
(SELECT TOP 1 A.EmployeeLocationID
FROM A
WHERE B.EmployeeLocation LIKE A.EmployeeLocation + '%'
ORDER BY LEN(A.EmployeeLocation) DESC)
WHERE B.EmployeeLocationID IS NULL

Roy Harvey
Beacon Falls, CT

On 27 Dec 2006 15:24:44 -0800, varkey.mathew@.wipro.com wrote:

Quote:

Originally Posted by

>Dear all,
>
>I need some help from all my Transact SQL Guru friends out there..
>
>Here is the scenario in its most simplified form.. ..
>
>I have two tables.. A(Lookup table) and B(Transaction Table)
>
>TableA Fields
EmployeeLocationID
EmployeeLocation (This could have values say
>"B","BO","BOM","C","CA","CALC") etc...
>
>
>TableB Fields
EmployeeID
EmployeeName......
EmployeeLocationID (will have null initially when rows are populated
>first time)
EmployeeLocation (This could have values
>"BA123","BOMBAY","BOTS123","BRACK"... etc)
>
>I hope you get where I am leading this to, from my examples..
>Requirement is to populate the EmployeeLocationID in Table B with
>EmployeeLocationID from TableA by matching the field EmployeeLocation
>in both tables.Please note that table B's EmployeeLocation could be A's
>EmployeeLocation + some additionalcodes like "123","RACK" etc in the
>above example...
>
>Therefore, this is what I had wrote initially..
>
>update B
>set B.EmployeeLocationID =A.EmployeeLocationID

Quote:

Originally Posted by

>>From B inner join A on B.EmployeeLocation Like A.EmployeeLocation +

>'%'
>where B.EmployeeLocationID is null
>
>This works fine alright.. However the trouble is that it doesn't cater
>to the complete requirement...
>
>For example the row in Table B with EmployeeLocation as "BOMBAY" will
>get the EmployeeLocationID for "B" or "BO" and not "BOM" because they
>are earlier rows in table A while comparing..The requirement is that we
>should get the EmployeeLocationID of "BOM" in this case... That is,
>the comparison should be done first for the maximum "maximum no of
>characters" match, then for the next "no of characters" match, then for
>the next "no of characters"match... etc...
>
>Therefore this is the expected match for my examples based on
>requirement..
>
>"BA123" from Table B should be mapped to EmployeeLocationID for "B" of
>Table A
>"BOMBAY" from Table B should be mapped to EmployeeLocationID for "BOM"
>of Table A
>"BOTS123" from Table B should be mapped to EmployeeLocationID for "BO"
>of Table A
>"BRACK" from Table B should be mapped to EmployeeLocationID for "B" of
>Table A
>
>
>Can someone please help me with my query, or atleast direct me to the
>right material so that I can take care of this requirement..
>
>
>Looking forward to hearing from someone ASAP.. Please help..
>
>Best regards,
>
>VM...

|||Why did you fail to post DDL, screw up the syntax and violate ISO-11179
naming rules? Probably because you also confuse fields and columns.
Let's start by cleaning up you code, so it looks like SQL.

SQL uses single quotes for strings. A data element can be a location or
an identifier, never both. A transaction is some kind of transaction.
Etc. You need a data modeling course. Your sample data failed to give
values of the improperly named 'EmployeeLocationID' - I hope to
ghod you are not using IDENTITY and thinking that it is a key!!
Don't you know about SAN and other industry standard address numbers?

Quote:

Originally Posted by

Quote:

Originally Posted by

>A(Lookup table) and B(Transaction Table) <<

Why did you avoid clear names?

CREATE TABLE LocationCodes
(loc_prefix VARCHAR(5) NOT NULL PRIMARY KEY,
loc_code INTEGER NOT NULL); -- industry SAN ??

-- put wildcards in the table for indexing
INSERT INTO LocationCodes VALUES ('B%', 100);
INSERT INTO LocationCodes VALUES ('BO%', 101);
INSERT INTO LocationCodes VALUES ('BOM%', 102);
Etc.

Can two prefixes belong to the same SAN? No specs given.

Without a key in that vague transactions table, you do not have a
proper table at all. I had to make up one. Why do you have employee
id and not find the employee name via a join to the Personnel table?
Isn't the idea of RDBMS to get rid of redudant data?

CREATE TABLE FoobarTrans
(foobar_trans_nbr INTEGER NOT NULL PRIMARY KEY,
-- CHECK (<<needs validation rule here>>),
emp_id INTEGER NOT NULL
REFERENCES Personnel(emp_id)
ON UPDATE CASCADE,
loc_code INTEGER NOT NULL
REFERENCES LocationCodes(loc_code)
ON UPDATE CASCADE,
Etc.);

The prefix should have been used when you inserted the initial row (NOT
field!!!) into the table. Because you are confusing fields and
columns, files and tables, you are thinking in procedural *steps* with
updates just like a punch card file, not in sets like an SQL
programmer.

Quote:

Originally Posted by

Quote:

Originally Posted by

>I hope you get where I am leading this to, from my examples.. <<

No. Clear specs would have been nice, along with real DDL.

Here is a skeleton of a proc for this. You can put Roy's SELECT TOP
in the VALUES list, but if you have SQL-2005, try this little untested
statement:

INSERT INTO FoobarTrans (foobar_trans_nbr, emp_id, ..)
VALUES (@.my_foobar_trans_nbr, @.my_emp_id,

(WITH (SELECT L1.loc_code, LEN(L1.loc_prefix)
FROM LocationCodes AS L1
WHERE L1.loc_prefix LIKE @.my_loc_prefix)
AS M(loc_code, fit)
SELECT loc_code
FROM M AS M1
WHERE M1.fit
= (SELECT MAX(M2.fit) FROM M AS M2)),

Etc.);

You will need error handling code for prefixes that do not match.|||Roy,

Thanks a tonne for your prompt and timely response... I could modify my
script on the lines of your code and it worked (smile)..

Celko,

Thanks to you as well, for your valuable suggestions... And I can
understand your outburst... I just jotted down something(without even
proof reading it) because the intend was to get the question out
yesterday, to hopefully get a response by today... Clear names were not
used, Redundancy was there etc... because it was a cooked up scenario,
but my requirement was very like the one I had outlined ...

I really appreciate the time you have taken to progressively take apart
my question... But as long as you understood the original intend on
where I was stuck and I got a solution to my problem, Believe me I am
happy...

I will remember that I might upset Guru's like you with my questions,
in future, and be more careful with its structure and wording...

Thanks once again...

VM

--CELKO-- wrote:

Quote:

Originally Posted by

Why did you fail to post DDL, screw up the syntax and violate ISO-11179
naming rules? Probably because you also confuse fields and columns.
Let's start by cleaning up you code, so it looks like SQL.
>
SQL uses single quotes for strings. A data element can be a location or
an identifier, never both. A transaction is some kind of transaction.
Etc. You need a data modeling course. Your sample data failed to give
values of the improperly named 'EmployeeLocationID' - I hope to
ghod you are not using IDENTITY and thinking that it is a key!!
Don't you know about SAN and other industry standard address numbers?
>
>

Quote:

Originally Posted by

Quote:

Originally Posted by

A(Lookup table) and B(Transaction Table) <<

>
Why did you avoid clear names?
>
CREATE TABLE LocationCodes
(loc_prefix VARCHAR(5) NOT NULL PRIMARY KEY,
loc_code INTEGER NOT NULL); -- industry SAN ??
>
-- put wildcards in the table for indexing
INSERT INTO LocationCodes VALUES ('B%', 100);
INSERT INTO LocationCodes VALUES ('BO%', 101);
INSERT INTO LocationCodes VALUES ('BOM%', 102);
Etc.
>
Can two prefixes belong to the same SAN? No specs given.
>
Without a key in that vague transactions table, you do not have a
proper table at all. I had to make up one. Why do you have employee
id and not find the employee name via a join to the Personnel table?
Isn't the idea of RDBMS to get rid of redudant data?
>
CREATE TABLE FoobarTrans
(foobar_trans_nbr INTEGER NOT NULL PRIMARY KEY,
-- CHECK (<<needs validation rule here>>),
emp_id INTEGER NOT NULL
REFERENCES Personnel(emp_id)
ON UPDATE CASCADE,
loc_code INTEGER NOT NULL
REFERENCES LocationCodes(loc_code)
ON UPDATE CASCADE,
Etc.);
>
The prefix should have been used when you inserted the initial row (NOT
field!!!) into the table. Because you are confusing fields and
columns, files and tables, you are thinking in procedural *steps* with
updates just like a punch card file, not in sets like an SQL
programmer.
>

Quote:

Originally Posted by

Quote:

Originally Posted by

I hope you get where I am leading this to, from my examples.. <<

>
No. Clear specs would have been nice, along with real DDL.
>
Here is a skeleton of a proc for this. You can put Roy's SELECT TOP
in the VALUES list, but if you have SQL-2005, try this little untested
statement:
>
INSERT INTO FoobarTrans (foobar_trans_nbr, emp_id, ..)
VALUES (@.my_foobar_trans_nbr, @.my_emp_id,
>
(WITH (SELECT L1.loc_code, LEN(L1.loc_prefix)
FROM LocationCodes AS L1
WHERE L1.loc_prefix LIKE @.my_loc_prefix)
AS M(loc_code, fit)
SELECT loc_code
FROM M AS M1
WHERE M1.fit
= (SELECT MAX(M2.fit) FROM M AS M2)),
>
Etc.);
>
You will need error handling code for prefixes that do not match.

REPLACE function doesnt work with null-bytes

Dear Community,

We have a problem with null-Bytes in varchar-Columns, which are not
handled correctly in our application. Therefor we try to filter them
out using the Transact-SQL REPLACE function.

The Problem was, that the REPLACE-function didn't behave the way we
expected.

Following Example demonstrates the behavior:

declare @.txt varchar(512)
declare @.i int
set @.txt = 'hello ' + char(0) + 'world'
print @.txt
set @.i = 1

while @.i <= len(@.txt)
begin
print str(@.i) + substring(@.txt, @.i, 1)
set @.i = @.i + 1
end

print 'Length: ' + str(len(@.txt))
print 'trying to replace null-byte:'
print replace(@.txt, char(0), '*')
print 'replace Letter h'
print replace(@.txt, 'h', char(39))

-- end example

Output:

hello
1h
2e
3l
4l
5o
6
7
8w
9o
10r
11l
12d
Length: 12
trying to replace null-byte:
*
replace Letter h
'ello

The Null-Byte replace destroys the whole string. This behavior occurs
only on some of our
databases. The others work correctly.

Is it possible that it depends on some server setting?

Thanks
Enno(enno@.berlin.cortex-software.de) writes:
> We have a problem with null-Bytes in varchar-Columns, which are not
> handled correctly in our application. Therefor we try to filter them
> out using the Transact-SQL REPLACE function.
> The Problem was, that the REPLACE-function didn't behave the way we
> expected.
>...
> The Null-Byte replace destroys the whole string. This behavior occurs
> only on some of our
> databases. The others work correctly.
> Is it possible that it depends on some server setting?

I've seen this before, but I was a little puzzled when you said that
it worked on some databases.

Playing around, I was lucky to find that it works if you have an SQL
collation. So in your script, change the crucial line to:

print replace(@.txt COLLATE SQL_Latin1_General_CP1_CI_AS, char(0), '*')

In SQL 2005, the string does not get destroyed, but neither does the
NULL get replaced. Since this is inconsistent, I filed bug for this on
http://lab.msdn.microsoft.com/Produ...ackId=FDBK45444
You can vote on it, if you like.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Would the database setting of ANSI Nulls have an effect on this
behavior? In 2000? In 2005?

-- Mark D Powell --|||Mark D Powell (Mark.Powell@.eds.com) writes:
> Would the database setting of ANSI Nulls have an effect on this
> behavior? In 2000? In 2005?

I haven't tested, but I find that very unlikely.

The effect of the database setting ANSI_NULLS when it's ON is that it forces
the SET option ANSI_NULLS to be in the ON position, even though the process
has it OFF. If the database setting is OFF, it has no effect, but the
setting of the process determines.

And to make it even messier, when you run a stored procedure, what counts is
the setting when the procedure was saved.

As far as I know, ANSI_NULLS has no effect on the interpretation of NUL
characters. ANSI_NULLS determines how things like "IF @.x = NULL" works.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for the answer.
We have cleaned our data using the sql-Collation.
I think it's not wise to have Data with NUL-bytes in a
varchar-Column, because other Transact-SQL-functions have also
problems.

For example the LIKE operator is also inconsistent.

SELECT 1
WHERE CHAR(0) LIKE '_'

Yields:
<nothing
SELECT 1
WHERE CHAR(65) LIKE '_'

Yields:
1

By using the collation you suggested, the LIKE Operator worked as
expected.

There must be some enviromental COLLATION setting, which is
database-specific, but i didn't find it in exported-scripts from the
enterprise manager.|||(enno@.berlin.cortex-software.de) writes:
> There must be some enviromental COLLATION setting, which is
> database-specific, but i didn't find it in exported-scripts from the
> enterprise manager.

In SQL Server there is first a *server collation* which defines the
collation for the system databases, and the default for new databases.

There is also a database collation, which defines the default collations
for new charcater columns in the database. If memory serves the database
collation also sets the collation for variables. However, temp-table
columns get their default from tempdb. If I remember correctly, table
variables gets their default from tempdb in SQL2000, but the database in SQL
2005.

When you create a table column, you can always explicitly set the collation.

And finally, when you have a character expression, you can always cast
to another collation.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Replace Function

Dear All,
In my Item Master table, the item column has values like 'printing' in more
than 1000 rows. This was a data entry error. Now my user want all the
instances of 'printing' to be replace by 'printed'. Is there any ways to
update the entire rows in one go, or I will have to change the name from VB
ror by row, or using cusror in SQL.
Please help.
Thanks & Regards,
PramodYou didn't provide much details, but assuming you have a column named "x" fo
r which some rows
currently have the string "printing" and you want to change that string to "
printed", then it is
dead simple:
UPDATE tblname
SET x = 'printed'
WHERE x = 'printing'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<pramod@.luxoroffice.com> wrote in message news:%235smP8lHFHA.3500@.TK2MSFTNGP14.phx.gbl...[c
olor=darkred]
> Dear All,
> In my Item Master table, the item column has values like 'printing' in mor
e
> than 1000 rows. This was a data entry error. Now my user want all the
> instances of 'printing' to be replace by 'printed'. Is there any ways to
> update the entire rows in one go, or I will have to change the name from V
B
> ror by row, or using cusror in SQL.
> Please help.
> Thanks & Regards,
> Pramod
>[/color]|||If when you say "values like 'printing'", you mean that
'printing' is not the entire column value, you may need
something like this.
update ItemMaster set
item = replace(item, 'printing', 'printed')
where item like '%printing%'
Steve Kass
Drew University
pramod@.luxoroffice.com wrote:

>Dear All,
>In my Item Master table, the item column has values like 'printing' in more
>than 1000 rows. This was a data entry error. Now my user want all the
>instances of 'printing' to be replace by 'printed'. Is there any ways to
>update the entire rows in one go, or I will have to change the name from VB
>ror by row, or using cusror in SQL.
>Please help.
>Thanks & Regards,
>Pramod
>
>|||try this:-
update <tablename>
set <fieldname> = replace(<fieldname>, 'Printing', 'Printed')
from table
steve
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23d4ofZmHFHA.1392@.TK2MSFTNGP10.phx.gbl...
> If when you say "values like 'printing'", you mean that
> 'printing' is not the entire column value, you may need
> something like this.
> update ItemMaster set
> item = replace(item, 'printing', 'printed')
> where item like '%printing%'
> Steve Kass
> Drew University
> pramod@.luxoroffice.com wrote:
>|||Dear Steve,
My Problem is sloved by the query u provided:
UPDATE ItemMaster
SET ItemName = REPLACE(ItemName, 'Printing', 'Printed')
FROM ItemMaster
WHERE ItemName LIKE '%Printing%'
Thanks,
"Steven Richardson" <steve@.cheltenhamdirectory.com> wrote in message
news:Ocz9w2mHFHA.1860@.TK2MSFTNGP15.phx.gbl...
> try this:-
> update <tablename>
> set <fieldname> = replace(<fieldname>, 'Printing', 'Printed')
> from table
>
> steve
>
> "Steve Kass" <skass@.drew.edu> wrote in message
> news:%23d4ofZmHFHA.1392@.TK2MSFTNGP10.phx.gbl...
>

Repeat Report Title On EveryPage

Dear Anyone,
All reports have report titles especially in RS. These are usually big textboxes that can be found in the beginning of the report.

When a report is executed, normally, these report titles, dissapear once we opted to go to another page of the report.

Is there a way to make the report titles repeat on each page?
Thanks,
Joseph OlleroYou could put it in the Page Header section.|||Any idea on how to do this on Report Builder?|||I read in another thread that it was not possible in RB.

dlesko|||

But you can't put Data in the Page Header section.

Lets say I have an Invoice. I want the Invoice Number and Customer information to show at the top of each page of the report.

Actually I want it to be configurable. using a report parameter.

Dave

|||

Hi Dave

DGNSP posted a solution to your problem in this thread... http://forums.microsoft.com/technet/ShowPost.aspx?postid=1379855&siteid=17

but it doesn't help poor old me!

Good luck!

Graham

Repeat Report Title On EveryPage

Dear Anyone,
All reports have report titles especially in RS. These are usually big textboxes that can be found in the beginning of the report.

When a report is executed, normally, these report titles, dissapear once we opted to go to another page of the report.

Is there a way to make the report titles repeat on each page?
Thanks,
Joseph OlleroYou could put it in the Page Header section.|||Any idea on how to do this on Report Builder?|||I read in another thread that it was not possible in RB.

dlesko|||

But you can't put Data in the Page Header section.

Lets say I have an Invoice. I want the Invoice Number and Customer information to show at the top of each page of the report.

Actually I want it to be configurable. using a report parameter.

Dave

|||

Hi Dave

DGNSP posted a solution to your problem in this thread... http://forums.microsoft.com/technet/ShowPost.aspx?postid=1379855&siteid=17

but it doesn't help poor old me!

Good luck!

Graham

Repeat Report Title On EveryPage

Dear Anyone,
All reports have report titles especially in RS. These are usually big textboxes that can be found in the beginning of the report.

When a report is executed, normally, these report titles, dissapear once we opted to go to another page of the report.

Is there a way to make the report titles repeat on each page?
Thanks,
Joseph OlleroYou could put it in the Page Header section.|||Any idea on how to do this on Report Builder?|||I read in another thread that it was not possible in RB.

dlesko|||

But you can't put Data in the Page Header section.

Lets say I have an Invoice. I want the Invoice Number and Customer information to show at the top of each page of the report.

Actually I want it to be configurable. using a report parameter.

Dave

|||

Hi Dave

DGNSP posted a solution to your problem in this thread... http://forums.microsoft.com/technet/ShowPost.aspx?postid=1379855&siteid=17

but it doesn't help poor old me!

Good luck!

Graham