How to free up the space ...

Hi all,
I have a table with column A char(1000) and then alter the table to
varchar(1000) and update tableA set A = rtrim(A)
I know a stupid way to free up the space by create a new table and move the
data to the new table. Is there a command to free up the empty space?
Thanks in advance!Hi
DBCC DBREINDEX (if your table has indexes) , or DBCC SHRINKFILE
"Atenza" <Atenza@.mail.hongkong.com> wrote in message
news:eVFE1vPkGHA.3816@.TK2MSFTNGP02.phx.gbl...
> Hi all,
> I have a table with column A char(1000) and then alter the table to
> varchar(1000) and update tableA set A = rtrim(A)
> I know a stupid way to free up the space by create a new table and move
> the data to the new table. Is there a command to free up the empty space?
> Thanks in advance!
>|||Hi,
The update using rtrim was not needed here as SQL Server does not store
spaces after the data. If you insert 'Pink Floyd ' SQL Server only
stores 'Pink Floyd'. Changing to varchar was enough.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Atenza" wrote:
> Hi all,
> I have a table with column A char(1000) and then alter the table to
> varchar(1000) and update tableA set A = rtrim(A)
> I know a stupid way to free up the space by create a new table and move the
> data to the new table. Is there a command to free up the empty space?
> Thanks in advance!
>
>|||Hi Ben,
I have done few tests on char and varchar. I found that insert 'Pink Floyd
' is different from ''Pink Floyd' into varchar. SQL Server does not store
spaces after the data applied to SQLSever 2005? coz i am using SQLSever
2000, is this the reason?
In CASE 1, use char(100)
In CASE 2, use varchar(100) by insert 'Pink Floyd '
In CASE 3, use varchar(100) by insert 'Pink Floyd'
In CASE 4, use char(100) and alter to varchar(100)
In CASE 5, use char(100) and move to new table varchar(100)
In CASE 4, for new create data, i think it should be saved spaces. But for
the old data, it seems that spaces cannot be released.
It seems that only CASE 5 can free up spaces. Is CASE 5 the only solution or
i have misunderstand something?
Here is my test result:
CASE 1:
CREATE TABLE dbo.Table1
(
col1 char(100) NOT NULL
) ON [PRIMARY]
insert into table1 values('Pink Floyd ')
while (select count(*) from table1) < 100000
insert into table1 select * from table1
sp_spaceused table1
name rows reserved data index_size
unused
-- -- -- -- --
--
Table1 131072 14792 KB 14768 KB 8 KB
16 KB
db size 16128KB
CASE 2:
CREATE TABLE dbo.Table2
(
col1 varchar(100) NOT NULL
) ON [PRIMARY]
insert into table2 values('Pink Floyd ')
while (select count(*) from table2) < 100000
insert into table2 select * from table2
sp_spaceused table2
name rows reserved data index_size
unused
-- -- -- -- --
--
Table1 131072 4552 KB 4488 KB 8 KB
56 KB
db size 5504KB
CASE 3:
CREATE TABLE dbo.Table3
(
col1 varchar(100) NOT NULL
) ON [PRIMARY]
insert into table3 values('Pink Floyd')
while (select count(*) from table3) < 100000
insert into table3 select * from table3
sp_spaceused table3
name rows reserved data index_size
unused
-- -- -- -- --
--
Table3 131072 3144 KB 3136 KB 8 KB
0 KB
db size 4096KB
CASE 4:
CREATE TABLE dbo.Table1
(
col1 char(100) NOT NULL
) ON [PRIMARY]
insert into table1 values('Pink Floyd ')
while (select count(*) from table1) < 100000
insert into table1 select * from table1
sp_spaceused table1
name rows reserved data index_size
unused
-- -- -- -- --
--
Table1 131072 14792 KB 14768 KB 8 KB
16 KB
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_Table1
(
col1 varchar(100) NOT NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.Table1)
EXEC('INSERT INTO dbo.Tmp_Table1 (col1)
SELECT CONVERT(varchar(100), col1) FROM dbo.Table1 TABLOCKX')
GO
DROP TABLE dbo.Table1
GO
EXECUTE sp_rename N'dbo.Tmp_Table1', N'Table1', 'OBJECT'
GO
COMMIT
sp_spaceused table1
name rows reserved data index_size
unused
-- -- -- -- --
--
Table1 131072 15240 KB 15200 KB 8 KB
32 KB
CASE 5:
CREATE TABLE dbo.Table1
(
col1 char(100) NOT NULL
) ON [PRIMARY]
insert into table1 values('Pink Floyd ')
while (select count(*) from table1) < 100000
insert into table1 select * from table1
sp_spaceused table1
name rows reserved data index_size
unused
-- -- -- -- --
--
Table1 131072 14792 KB 14768 KB 8 KB
16 KB
CREATE TABLE dbo.Table3
(
col1 varchar(100) NOT NULL
) ON [PRIMARY]
insert into table3 select rtrim(col1) from table1
sp_spaceused table3
name rows reserved data index_size
unused
-- -- -- -- --
--
Table3 131072 3144 KB 3136 KB 8 KB
0 KB
"Ben Nevarez" <bnevarez@.sjm.com> wrote in message
news:ABD23E33-63E4-4C3B-A0A1-179A2B5E2706@.microsoft.com...
> Hi,
> The update using rtrim was not needed here as SQL Server does not store
> spaces after the data. If you insert 'Pink Floyd ' SQL Server
> only
> stores 'Pink Floyd'. Changing to varchar was enough.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Atenza" wrote:
>> Hi all,
>> I have a table with column A char(1000) and then alter the table to
>> varchar(1000) and update tableA set A = rtrim(A)
>> I know a stupid way to free up the space by create a new table and move
>> the
>> data to the new table. Is there a command to free up the empty space?
>> Thanks in advance!
>>|||> SQL Server does not store
> spaces after the data applied to SQLSever 2005?
For char, SQL Server always store the specified length. It pads the string with spaces.
For varchar, SQL Server by default store trailing spaces. Whether or not to do this depends on the
setting of ANSI_PADDING when the table/column is *created*.
Rebuilding the indexes on the table should give you back the space. If the table doesn't have a
clustered index, then you either have to create on (and possibly drop it), or export/import.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Atenza" <Atenza@.mail.hongkong.com> wrote in message news:utQ2DbRkGHA.3816@.TK2MSFTNGP02.phx.gbl...
> Hi Ben,
> I have done few tests on char and varchar. I found that insert 'Pink Floyd ' is different from
> ''Pink Floyd' into varchar. SQL Server does not store
> spaces after the data applied to SQLSever 2005? coz i am using SQLSever 2000, is this the reason?
> In CASE 1, use char(100)
> In CASE 2, use varchar(100) by insert 'Pink Floyd '
> In CASE 3, use varchar(100) by insert 'Pink Floyd'
> In CASE 4, use char(100) and alter to varchar(100)
> In CASE 5, use char(100) and move to new table varchar(100)
> In CASE 4, for new create data, i think it should be saved spaces. But for the old data, it seems
> that spaces cannot be released.
> It seems that only CASE 5 can free up spaces. Is CASE 5 the only solution or i have misunderstand
> something?
>
> Here is my test result:
> CASE 1:
> CREATE TABLE dbo.Table1
> (
> col1 char(100) NOT NULL
> ) ON [PRIMARY]
> insert into table1 values('Pink Floyd ')
> while (select count(*) from table1) < 100000
> insert into table1 select * from table1
> sp_spaceused table1
> name rows reserved data index_size unused
> -- -- -- -- --
> --
> Table1 131072 14792 KB 14768 KB 8 KB 16 KB
> db size 16128KB
>
> CASE 2:
> CREATE TABLE dbo.Table2
> (
> col1 varchar(100) NOT NULL
> ) ON [PRIMARY]
> insert into table2 values('Pink Floyd ')
> while (select count(*) from table2) < 100000
> insert into table2 select * from table2
> sp_spaceused table2
> name rows reserved data index_size unused
> -- -- -- -- --
> --
> Table1 131072 4552 KB 4488 KB 8 KB 56 KB
> db size 5504KB
>
> CASE 3:
> CREATE TABLE dbo.Table3
> (
> col1 varchar(100) NOT NULL
> ) ON [PRIMARY]
> insert into table3 values('Pink Floyd')
> while (select count(*) from table3) < 100000
> insert into table3 select * from table3
> sp_spaceused table3
> name rows reserved data index_size unused
> -- -- -- -- --
> --
> Table3 131072 3144 KB 3136 KB 8 KB 0 KB
> db size 4096KB
>
> CASE 4:
> CREATE TABLE dbo.Table1
> (
> col1 char(100) NOT NULL
> ) ON [PRIMARY]
> insert into table1 values('Pink Floyd ')
> while (select count(*) from table1) < 100000
> insert into table1 select * from table1
> sp_spaceused table1
> name rows reserved data index_size unused
> -- -- -- -- --
> --
> Table1 131072 14792 KB 14768 KB 8 KB 16 KB
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> BEGIN TRANSACTION
> CREATE TABLE dbo.Tmp_Table1
> (
> col1 varchar(100) NOT NULL
> ) ON [PRIMARY]
> GO
> IF EXISTS(SELECT * FROM dbo.Table1)
> EXEC('INSERT INTO dbo.Tmp_Table1 (col1)
> SELECT CONVERT(varchar(100), col1) FROM dbo.Table1 TABLOCKX')
> GO
> DROP TABLE dbo.Table1
> GO
> EXECUTE sp_rename N'dbo.Tmp_Table1', N'Table1', 'OBJECT'
> GO
> COMMIT
> sp_spaceused table1
> name rows reserved data index_size unused
> -- -- -- -- --
> --
> Table1 131072 15240 KB 15200 KB 8 KB 32 KB
>
>
> CASE 5:
> CREATE TABLE dbo.Table1
> (
> col1 char(100) NOT NULL
> ) ON [PRIMARY]
> insert into table1 values('Pink Floyd ')
> while (select count(*) from table1) < 100000
> insert into table1 select * from table1
> sp_spaceused table1
> name rows reserved data index_size unused
> -- -- -- -- --
> --
> Table1 131072 14792 KB 14768 KB 8 KB 16 KB
> CREATE TABLE dbo.Table3
> (
> col1 varchar(100) NOT NULL
> ) ON [PRIMARY]
> insert into table3 select rtrim(col1) from table1
> sp_spaceused table3
> name rows reserved data index_size unused
> -- -- -- -- --
> --
> Table3 131072 3144 KB 3136 KB 8 KB 0 KB
>
>
>
> "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> news:ABD23E33-63E4-4C3B-A0A1-179A2B5E2706@.microsoft.com...
>> Hi,
>> The update using rtrim was not needed here as SQL Server does not store
>> spaces after the data. If you insert 'Pink Floyd ' SQL Server only
>> stores 'Pink Floyd'. Changing to varchar was enough.
>> Ben Nevarez, MCDBA, OCP
>> Database Administrator
>>
>> "Atenza" wrote:
>> Hi all,
>> I have a table with column A char(1000) and then alter the table to
>> varchar(1000) and update tableA set A = rtrim(A)
>> I know a stupid way to free up the space by create a new table and move the
>> data to the new table. Is there a command to free up the empty space?
>> Thanks in advance!
>>
>|||thank you very much!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:utEoaQQkGHA.4672@.TK2MSFTNGP02.phx.gbl...
> Hi
> DBCC DBREINDEX (if your table has indexes) , or DBCC SHRINKFILE
>
> "Atenza" <Atenza@.mail.hongkong.com> wrote in message
> news:eVFE1vPkGHA.3816@.TK2MSFTNGP02.phx.gbl...
>> Hi all,
>> I have a table with column A char(1000) and then alter the table to
>> varchar(1000) and update tableA set A = rtrim(A)
>> I know a stupid way to free up the space by create a new table and move
>> the data to the new table. Is there a command to free up the empty space?
>> Thanks in advance!
>>
>|||thank you very much! it works!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uKxJ6QSkGHA.4652@.TK2MSFTNGP05.phx.gbl...
>> SQL Server does not store
>> spaces after the data applied to SQLSever 2005?
> For char, SQL Server always store the specified length. It pads the string
> with spaces.
> For varchar, SQL Server by default store trailing spaces. Whether or not
> to do this depends on the setting of ANSI_PADDING when the table/column is
> *created*.
> Rebuilding the indexes on the table should give you back the space. If the
> table doesn't have a clustered index, then you either have to create on
> (and possibly drop it), or export/import.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Atenza" <Atenza@.mail.hongkong.com> wrote in message
> news:utQ2DbRkGHA.3816@.TK2MSFTNGP02.phx.gbl...
>> Hi Ben,
>> I have done few tests on char and varchar. I found that insert 'Pink
>> Floyd ' is different from ''Pink Floyd' into varchar. SQL Server does not
>> store
>> spaces after the data applied to SQLSever 2005? coz i am using SQLSever
>> 2000, is this the reason?
>> In CASE 1, use char(100)
>> In CASE 2, use varchar(100) by insert 'Pink Floyd '
>> In CASE 3, use varchar(100) by insert 'Pink Floyd'
>> In CASE 4, use char(100) and alter to varchar(100)
>> In CASE 5, use char(100) and move to new table varchar(100)
>> In CASE 4, for new create data, i think it should be saved spaces. But
>> for the old data, it seems that spaces cannot be released.
>> It seems that only CASE 5 can free up spaces. Is CASE 5 the only solution
>> or i have misunderstand something?
>>
>> Here is my test result:
>> CASE 1:
>> CREATE TABLE dbo.Table1
>> (
>> col1 char(100) NOT NULL
>> ) ON [PRIMARY]
>> insert into table1 values('Pink Floyd ')
>> while (select count(*) from table1) < 100000
>> insert into table1 select * from table1
>> sp_spaceused table1
>> name rows reserved data index_size
>> unused
>> -- -- -- -- --
>> --
>> Table1 131072 14792 KB 14768 KB 8 KB 16 KB
>> db size 16128KB
>>
>> CASE 2:
>> CREATE TABLE dbo.Table2
>> (
>> col1 varchar(100) NOT NULL
>> ) ON [PRIMARY]
>> insert into table2 values('Pink Floyd ')
>> while (select count(*) from table2) < 100000
>> insert into table2 select * from table2
>> sp_spaceused table2
>> name rows reserved data index_size
>> unused
>> -- -- -- -- --
>> --
>> Table1 131072 4552 KB 4488 KB 8 KB 56 KB
>> db size 5504KB
>>
>> CASE 3:
>> CREATE TABLE dbo.Table3
>> (
>> col1 varchar(100) NOT NULL
>> ) ON [PRIMARY]
>> insert into table3 values('Pink Floyd')
>> while (select count(*) from table3) < 100000
>> insert into table3 select * from table3
>> sp_spaceused table3
>> name rows reserved data index_size
>> unused
>> -- -- -- -- --
>> --
>> Table3 131072 3144 KB 3136 KB 8 KB 0 KB
>> db size 4096KB
>>
>> CASE 4:
>> CREATE TABLE dbo.Table1
>> (
>> col1 char(100) NOT NULL
>> ) ON [PRIMARY]
>> insert into table1 values('Pink Floyd ')
>> while (select count(*) from table1) < 100000
>> insert into table1 select * from table1
>> sp_spaceused table1
>> name rows reserved data index_size
>> unused
>> -- -- -- -- --
>> --
>> Table1 131072 14792 KB 14768 KB 8 KB 16 KB
>> BEGIN TRANSACTION
>> SET QUOTED_IDENTIFIER ON
>> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
>> SET ARITHABORT ON
>> SET NUMERIC_ROUNDABORT OFF
>> SET CONCAT_NULL_YIELDS_NULL ON
>> SET ANSI_NULLS ON
>> SET ANSI_PADDING ON
>> SET ANSI_WARNINGS ON
>> COMMIT
>> BEGIN TRANSACTION
>> CREATE TABLE dbo.Tmp_Table1
>> (
>> col1 varchar(100) NOT NULL
>> ) ON [PRIMARY]
>> GO
>> IF EXISTS(SELECT * FROM dbo.Table1)
>> EXEC('INSERT INTO dbo.Tmp_Table1 (col1)
>> SELECT CONVERT(varchar(100), col1) FROM dbo.Table1 TABLOCKX')
>> GO
>> DROP TABLE dbo.Table1
>> GO
>> EXECUTE sp_rename N'dbo.Tmp_Table1', N'Table1', 'OBJECT'
>> GO
>> COMMIT
>> sp_spaceused table1
>> name rows reserved data index_size
>> unused
>> -- -- -- -- --
>> --
>> Table1 131072 15240 KB 15200 KB 8 KB 32 KB
>>
>>
>> CASE 5:
>> CREATE TABLE dbo.Table1
>> (
>> col1 char(100) NOT NULL
>> ) ON [PRIMARY]
>> insert into table1 values('Pink Floyd ')
>> while (select count(*) from table1) < 100000
>> insert into table1 select * from table1
>> sp_spaceused table1
>> name rows reserved data index_size
>> unused
>> -- -- -- -- --
>> --
>> Table1 131072 14792 KB 14768 KB 8 KB 16 KB
>> CREATE TABLE dbo.Table3
>> (
>> col1 varchar(100) NOT NULL
>> ) ON [PRIMARY]
>> insert into table3 select rtrim(col1) from table1
>> sp_spaceused table3
>> name rows reserved data index_size
>> unused
>> -- -- -- -- --
>> --
>> Table3 131072 3144 KB 3136 KB 8 KB 0 KB
>>
>>
>>
>> "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
>> news:ABD23E33-63E4-4C3B-A0A1-179A2B5E2706@.microsoft.com...
>> Hi,
>> The update using rtrim was not needed here as SQL Server does not store
>> spaces after the data. If you insert 'Pink Floyd ' SQL Server
>> only
>> stores 'Pink Floyd'. Changing to varchar was enough.
>> Ben Nevarez, MCDBA, OCP
>> Database Administrator
>>
>> "Atenza" wrote:
>> Hi all,
>> I have a table with column A char(1000) and then alter the table to
>> varchar(1000) and update tableA set A = rtrim(A)
>> I know a stupid way to free up the space by create a new table and move
>> the
>> data to the new table. Is there a command to free up the empty space?
>> Thanks in advance!
>>
>>
>

how to format in to dd/mm/yyyy ?

hi all,

i have table field name call

Start_date varchar(16)

when i select data from that filed values it gives me

Eg:

select Start_date from Customer

20011224 00:00:0
20011004 00:00:0

but i want to convert this data in to dd/mm/yyyy format ?

like ! 24/12/2001

04/10/2001

how do i do this task ?

regards

sujithf

create table #format (

start_Date_time varchar(16)

)

insert into #format values('20011224 00:00:0')

select convert(varchar(16), cast(start_date_time as datetime), 103) from #format

--103 is a British/French date format "dd/mm/yyyy"

|||

thanks very much.....

regards

sujithf

How to format a SELECT resultset as a comma separated list?

Hi,
I have the following need.
DECLARE @.Sql varchar(3000)
SET @.Sql = 'SELECT ' +
(SELECT name FROM pbajunio.sys.all_columns
WHERE object_id = object_id(@.TableName,'U')
AND system_type_id in (35,99,167,175,231,239))
+ ' FROM ' + @.TableName
--
I would like the inner SELECT (which produces a one column resultset) to be
formatted as a list separated by commas whose elements are the values of the
column of each row in the resultset.
Is this possible?
Thanks in advance,
Juan Dent, M.Sc.Hello, Juan
See: http://www.aspfaq.com/show.asp?id=2529
Razvan|||Awesome article! I hadn't seen the FOR XML trick. That is very neat.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1138211041.333378.270990@.g43g2000cwa.googlegroups.com...
> Hello, Juan
> See: http://www.aspfaq.com/show.asp?id=2529
> Razvan
>|||"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:eibExsdIGHA.3984@.TK2MSFTNGP14.phx.gbl...
> Awesome article! I hadn't seen the FOR XML trick. That is very neat.
Yes,interesting.It will also be interesting to see what 'those in the know'
who have ranted that such things should be in done in the client have
to say about this:)Some will find a delicious irony in that we are
talking xml in a relational database engine let alone this particular
(unentended consequence I would bet:) solution:)
www.rac4sql.net|||Ironic, No.
Interesting, Yes
That anyone would depend on "Unintended Consequences" and other
un-documented side effects in code that can easily break after the next
Service Pack, is, very interesting :-)
"05ponyGT" <nospam@.nospam> wrote in message
news:ehV76BgIGHA.3492@.TK2MSFTNGP09.phx.gbl...
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:eibExsdIGHA.3984@.TK2MSFTNGP14.phx.gbl...
> Yes,interesting.It will also be interesting to see what 'those in the
> know'
> who have ranted that such things should be in done in the client have
> to say about this:)Some will find a delicious irony in that we are
> talking xml in a relational database engine let alone this particular
> (unentended consequence I would bet:) solution:)
> www.rac4sql.net
>|||Hmm...perhaps you should clarify just who is the intended
target of your arrow...?
Or is my paranoia showing...:)
"Dave Frommer" <anti@.spam.com> wrote in message
news:Oy88GPhIGHA.1836@.TK2MSFTNGP11.phx.gbl...
> Ironic, No.
> Interesting, Yes
> That anyone would depend on "Unintended Consequences" and other
> un-documented side effects in code that can easily break after the next
> Service Pack, is, very interesting :-)
>
> "05ponyGT" <nospam@.nospam> wrote in message
> news:ehV76BgIGHA.3492@.TK2MSFTNGP09.phx.gbl...
>|||The intended target is:
"anyone who would depend on "Unintended Consequences" and other
un-documented side effects in code that can easily break after the next
Service Pack"
<grin>
"05ponyGT" <nospam@.nospam> wrote in message
news:O%23pK8XhIGHA.1728@.TK2MSFTNGP09.phx.gbl...
> Hmm...perhaps you should clarify just who is the intended
> target of your arrow...?
> Or is my paranoia showing...:)
> "Dave Frommer" <anti@.spam.com> wrote in message
> news:Oy88GPhIGHA.1836@.TK2MSFTNGP11.phx.gbl...
>|||Please read at least one book -- ANY BOOK -- on RDBMS before you code.
Go to one of the first chapters and learn what FIRST nORMAL FORM (1NF)
and why it is the very foundations of SQL.
Yes, there are stinking dirty kludges to violate 1NF and the entire
concept of tiered architecture. hey, if you really want to mess up
everything and slow down your code, add XML to the mix!|||Trying to kill 2 birds with one stone....cute.
:)
"Dave Frommer" <anti@.spam.com> wrote in message
news:O7msVohIGHA.2900@.TK2MSFTNGP14.phx.gbl...
> The intended target is:
> "anyone who would depend on "Unintended Consequences" and other
> un-documented side effects in code that can easily break after the next
> Service Pack"
> <grin>
> "05ponyGT" <nospam@.nospam> wrote in message
> news:O%23pK8XhIGHA.1728@.TK2MSFTNGP09.phx.gbl...
neat.
>|||Would you really feel it a violation of 1nf to return data this way? Not
store it (that would be a "sin") but just to view it.
I completely agree that this kind of thing shouldn't be done in the data
tier, but where would you draw the line? Are aggregates wrong? Should we
not be summing data in SQL? Or adding? Should SQL simply be used to store
data? I mean, why is it always wrong to do any kind of data manipulation
here where it is easy to do in a few lines of (mostly) relational
programming?
For starters, say we have the following set (the first two columns are
functionally dependent on one another, and there would likely be more
columns):
1, 200, "Joe, Jerry, Jimmy, JoeBob",
2, 300, "Fred, Filbert"
Is it not more efficient to do this with one line of SQL code, instead of
returning:
1, 200, "Joe"
1, 200, "Jerry"
1, 200, "Jimmy"
1, 200, "JoeBob"
2, 300, "Fred"
2, 300, "Filbert"
Or two resultsets and have to write iterative code that iterates over the
set?
The first format is a great way to return data to a report writer, because
the second set can be annoying (because even when grouping this will be 4
rows for 1, and 2 rows for 2. So if 200 happened to be a number that you
had to do math on, you would either have to do some sort of division to
change it to 1, 50 on each row, or something else.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1138241669.307414.84260@.z14g2000cwz.googlegroups.com...
> Please read at least one book -- ANY BOOK -- on RDBMS before you code.
> Go to one of the first chapters and learn what FIRST nORMAL FORM (1NF)
> and why it is the very foundations of SQL.
> Yes, there are stinking dirty kludges to violate 1NF and the entire
> concept of tiered architecture. hey, if you really want to mess up
> everything and slow down your code, add XML to the mix!
>

How to find trailing spaces in a column

Is there a way to find out (Script, tool e.t.c) if there
are any trailing spaces in a varchar column '
Please correct me if I am wrong but if you specify a
varchar column with 255 and enter only 15 character, it
should occupy only 15 Right '
It seems like we have varchar columns in several tables
with trailing spaces. I am thinking it is an application
problem but could it be SQL Server problem '
Thanks for any help.........> Is there a way to find out (Script, tool e.t.c) if there
> are any trailing spaces in a varchar column '
One method:
SELECT MyColumn from MyTable
WHERE DATALENGTH(MyColumn) <> DATALENGTH(RTRIM(MyColumn))
> Please correct me if I am wrong but if you specify a
> varchar column with 255 and enter only 15 character, it
> should occupy only 15 Right '
Correct (plus overhead).
> It seems like we have varchar columns in several tables
> with trailing spaces. I am thinking it is an application
> problem but could it be SQL Server problem '
Probably an application issue.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve" <anonymous@.discussions.microsoft.com> wrote in message
news:1a84e01c44eec$32d0a740$a301280a@.phx.gbl...
> Is there a way to find out (Script, tool e.t.c) if there
> are any trailing spaces in a varchar column '
> Please correct me if I am wrong but if you specify a
> varchar column with 255 and enter only 15 character, it
> should occupy only 15 Right '
> It seems like we have varchar columns in several tables
> with trailing spaces. I am thinking it is an application
> problem but could it be SQL Server problem '
> Thanks for any help.........|||Steve,
> Please correct me if I am wrong but if you specify a
> varchar column with 255 and enter only 15 character, it
> should occupy only 15 Right '
Correct. However, the application might include trailing spaces in the INSERT statement. Whether SQL Server
will keep those of not depends on the setting of ANSI_PADDINGS when the table (or column) was created. Use
"sp_help tblname" to find out.
To find rows with trailing spaces, you can do something like (be prepared for a table scan):
SELECT * FROM authors WHERE SUBSTRING(REVERSE(au_lname), 1, 1) = ' '
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve" <anonymous@.discussions.microsoft.com> wrote in message news:1a84e01c44eec$32d0a740$a301280a@.phx.gbl...
> Is there a way to find out (Script, tool e.t.c) if there
> are any trailing spaces in a varchar column '
> Please correct me if I am wrong but if you specify a
> varchar column with 255 and enter only 15 character, it
> should occupy only 15 Right '
> It seems like we have varchar columns in several tables
> with trailing spaces. I am thinking it is an application
> problem but could it be SQL Server problem '
> Thanks for any help.........|||Thanks for all your help Dan & Tibor.....
>--Original Message--
>Is there a way to find out (Script, tool e.t.c) if there
>are any trailing spaces in a varchar column '
>Please correct me if I am wrong but if you specify a
>varchar column with 255 and enter only 15 character, it
>should occupy only 15 Right '
>It seems like we have varchar columns in several tables
>with trailing spaces. I am thinking it is an application
>problem but could it be SQL Server problem '
>Thanks for any help.........
>.
>sql

How to find trailing spaces in a column

Is there a way to find out (Script, tool e.t.c) if there
are any trailing spaces in a varchar column ?
Please correct me if I am wrong but if you specify a
varchar column with 255 and enter only 15 character, it
should occupy only 15 Right ?
It seems like we have varchar columns in several tables
with trailing spaces. I am thinking it is an application
problem but could it be SQL Server problem ?
Thanks for any help.........
> Is there a way to find out (Script, tool e.t.c) if there
> are any trailing spaces in a varchar column ?
One method:
SELECT MyColumn from MyTable
WHERE DATALENGTH(MyColumn) <> DATALENGTH(RTRIM(MyColumn))

> Please correct me if I am wrong but if you specify a
> varchar column with 255 and enter only 15 character, it
> should occupy only 15 Right ?
Correct (plus overhead).

> It seems like we have varchar columns in several tables
> with trailing spaces. I am thinking it is an application
> problem but could it be SQL Server problem ?
Probably an application issue.
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve" <anonymous@.discussions.microsoft.com> wrote in message
news:1a84e01c44eec$32d0a740$a301280a@.phx.gbl...
> Is there a way to find out (Script, tool e.t.c) if there
> are any trailing spaces in a varchar column ?
> Please correct me if I am wrong but if you specify a
> varchar column with 255 and enter only 15 character, it
> should occupy only 15 Right ?
> It seems like we have varchar columns in several tables
> with trailing spaces. I am thinking it is an application
> problem but could it be SQL Server problem ?
> Thanks for any help.........
|||Steve,

> Please correct me if I am wrong but if you specify a
> varchar column with 255 and enter only 15 character, it
> should occupy only 15 Right ?
Correct. However, the application might include trailing spaces in the INSERT statement. Whether SQL Server
will keep those of not depends on the setting of ANSI_PADDINGS when the table (or column) was created. Use
"sp_help tblname" to find out.
To find rows with trailing spaces, you can do something like (be prepared for a table scan):
SELECT * FROM authors WHERE SUBSTRING(REVERSE(au_lname), 1, 1) = ' '
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve" <anonymous@.discussions.microsoft.com> wrote in message news:1a84e01c44eec$32d0a740$a301280a@.phx.gbl...
> Is there a way to find out (Script, tool e.t.c) if there
> are any trailing spaces in a varchar column ?
> Please correct me if I am wrong but if you specify a
> varchar column with 255 and enter only 15 character, it
> should occupy only 15 Right ?
> It seems like we have varchar columns in several tables
> with trailing spaces. I am thinking it is an application
> problem but could it be SQL Server problem ?
> Thanks for any help.........

How to find trailing spaces in a column

Is there a way to find out (Script, tool e.t.c) if there
are any trailing spaces in a varchar column '
Please correct me if I am wrong but if you specify a
varchar column with 255 and enter only 15 character, it
should occupy only 15 Right '
It seems like we have varchar columns in several tables
with trailing spaces. I am thinking it is an application
problem but could it be SQL Server problem '
Thanks for any help.........> Is there a way to find out (Script, tool e.t.c) if there
> are any trailing spaces in a varchar column '
One method:
SELECT MyColumn from MyTable
WHERE DATALENGTH(MyColumn) <> DATALENGTH(RTRIM(MyColumn))

> Please correct me if I am wrong but if you specify a
> varchar column with 255 and enter only 15 character, it
> should occupy only 15 Right '
Correct (plus overhead).

> It seems like we have varchar columns in several tables
> with trailing spaces. I am thinking it is an application
> problem but could it be SQL Server problem '
Probably an application issue.
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve" <anonymous@.discussions.microsoft.com> wrote in message
news:1a84e01c44eec$32d0a740$a301280a@.phx
.gbl...
> Is there a way to find out (Script, tool e.t.c) if there
> are any trailing spaces in a varchar column '
> Please correct me if I am wrong but if you specify a
> varchar column with 255 and enter only 15 character, it
> should occupy only 15 Right '
> It seems like we have varchar columns in several tables
> with trailing spaces. I am thinking it is an application
> problem but could it be SQL Server problem '
> Thanks for any help.........|||Steve,

> Please correct me if I am wrong but if you specify a
> varchar column with 255 and enter only 15 character, it
> should occupy only 15 Right '
Correct. However, the application might include trailing spaces in the INSER
T statement. Whether SQL Server
will keep those of not depends on the setting of ANSI_PADDINGS when the tabl
e (or column) was created. Use
"sp_help tblname" to find out.
To find rows with trailing spaces, you can do something like (be prepared fo
r a table scan):
SELECT * FROM authors WHERE SUBSTRING(REVERSE(au_lname), 1, 1) = ' '
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve" <anonymous@.discussions.microsoft.com> wrote in message news:1a84e01c44eec$32d0a740$a
301280a@.phx.gbl...
> Is there a way to find out (Script, tool e.t.c) if there
> are any trailing spaces in a varchar column '
> Please correct me if I am wrong but if you specify a
> varchar column with 255 and enter only 15 character, it
> should occupy only 15 Right '
> It seems like we have varchar columns in several tables
> with trailing spaces. I am thinking it is an application
> problem but could it be SQL Server problem '
> Thanks for any help.........

How to find the first not used Char with a SELECT

I have a table Names with two fields:
ChID varchar(1)
Name varchar(25)
where the ChID char is in a range a .. z
(there will never be more then 27 records)
Some chars are used, some not
Two examples:
eg.
I have 4 records: CharID b, c, e, and f are used
I want to find the first not used char
In this example it is the char a
eg.
I have 5 records: CharID a, b, c, e, and f are used
I want to find the first not used char
In this example it is the char d.
Is a SELECT statement possible for finding the first not used Char?
How would such a statement be?Hi Henk,
Assuming all your ChID's are lower case, and a-z in ASCII:
CREATE TABLE NAMES(ChID varchar(1), Name varchar(25) NULL)
INSERT INTO Names(ChID) VALUES('f')
INSERT INTO Names(ChID) VALUES('b')
INSERT INTO Names(ChID) VALUES('c')
INSERT INTO Names(ChID) VALUES('e')
SELECT MIN(CHAR(ASCII(ChID) - 1)) FROM Names
WHERE NOT EXISTS(SELECT NULL FROM Names n2 WHERE ASCII(n2.ChID) =ASCII(Names.ChID)-1 )
AND ChID > 'a'
DROP TABLE Names
How do you get _27_ rows btw?
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Henk Schreij" <henk@.schreijDOTnl> wrote in message
news:O$CUghtdDHA.1944@.TK2MSFTNGP12.phx.gbl...
> I have a table Names with two fields:
> ChID varchar(1)
> Name varchar(25)
> where the ChID char is in a range a .. z
> (there will never be more then 27 records)
> Some chars are used, some not
> Two examples:
> eg.
> I have 4 records: CharID b, c, e, and f are used
> I want to find the first not used char
> In this example it is the char a
> eg.
> I have 5 records: CharID a, b, c, e, and f are used
> I want to find the first not used char
> In this example it is the char d.
> Is a SELECT statement possible for finding the first not used Char?
> How would such a statement be?
>|||Jacco thanks,
It is a wonderful simple solution, exactly what I wanted.
You asked: How do you get _27_ rows btw?
Do you mean: How do you control that there will not be more then 27 rows?
The solution is that I use this SQL statement in a Delphi application. There
I use RecordCount, to limit the amount of records.
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> schreef in bericht
news:ur2NovtdDHA.1448@.TK2MSFTNGP12.phx.gbl...
> Hi Henk,
>
> Assuming all your ChID's are lower case, and a-z in ASCII:
> CREATE TABLE NAMES(ChID varchar(1), Name varchar(25) NULL)
> INSERT INTO Names(ChID) VALUES('f')
> INSERT INTO Names(ChID) VALUES('b')
> INSERT INTO Names(ChID) VALUES('c')
> INSERT INTO Names(ChID) VALUES('e')
> SELECT MIN(CHAR(ASCII(ChID) - 1)) FROM Names
> WHERE NOT EXISTS(SELECT NULL FROM Names n2 WHERE ASCII(n2.ChID) => ASCII(Names.ChID)-1 )
> AND ChID > 'a'
> DROP TABLE Names
> How do you get _27_ rows btw?
> --
> Jacco Schalkwijk MCDBA, MCSD, MCSE
> Database Administrator
> Eurostop Ltd.
>
> "Henk Schreij" <henk@.schreijDOTnl> wrote in message
> news:O$CUghtdDHA.1944@.TK2MSFTNGP12.phx.gbl...
> > I have a table Names with two fields:
> > ChID varchar(1)
> > Name varchar(25)
> > where the ChID char is in a range a .. z
> > (there will never be more then 27 records)
> > Some chars are used, some not
> >
> > Two examples:
> > eg.
> > I have 4 records: CharID b, c, e, and f are used
> > I want to find the first not used char
> > In this example it is the char a
> > eg.
> > I have 5 records: CharID a, b, c, e, and f are used
> > I want to find the first not used char
> > In this example it is the char d.
> >
> > Is a SELECT statement possible for finding the first not used Char?
> > How would such a statement be?
> >
> >
>|||the previous code won't work in case 'abc' (must be 'd') and in case of
empty table (must be 'a'). this one looks not so fine, but works
declare @.id varchar(1), @.fo varchar(1)
set @.fo='a'
declare MV cursor for select distinct lower(ChID) from Names where ChID
between 'a' and 'z' order by 1
Open MV
FETCH NEXT FROM MV INTO @.id
WHILE @.@.FETCH_STATUS = 0
BEGIN
if @.fo<>@.id BREAK
set @.fo=(CHAR(ASCII(@.fo) + 1))
FETCH NEXT FROM MV INTO @.id
END
CLOSE MV
DEALLOCATE MV
IF ASCII(@.fo)>122 set @.fo=NULL
print ISNULL(@.fo,'-')|||Hi Henk,
I meant that usually there are only 26 letters from a-z (well, definitly in
ASCII), so I was wondering where you got the 27th from?
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Henk Schreij" <henk@.schreijDOTnl> wrote in message
news:%238Xc8$wdDHA.1944@.TK2MSFTNGP12.phx.gbl...
> Jacco thanks,
> It is a wonderful simple solution, exactly what I wanted.
> You asked: How do you get _27_ rows btw?
> Do you mean: How do you control that there will not be more then 27 rows?
> The solution is that I use this SQL statement in a Delphi application.
There
> I use RecordCount, to limit the amount of records.
> "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> schreef in bericht
> news:ur2NovtdDHA.1448@.TK2MSFTNGP12.phx.gbl...
> > Hi Henk,
> >
> >
> > Assuming all your ChID's are lower case, and a-z in ASCII:
> >
> > CREATE TABLE NAMES(ChID varchar(1), Name varchar(25) NULL)
> >
> > INSERT INTO Names(ChID) VALUES('f')
> > INSERT INTO Names(ChID) VALUES('b')
> > INSERT INTO Names(ChID) VALUES('c')
> > INSERT INTO Names(ChID) VALUES('e')
> >
> > SELECT MIN(CHAR(ASCII(ChID) - 1)) FROM Names
> > WHERE NOT EXISTS(SELECT NULL FROM Names n2 WHERE ASCII(n2.ChID) => > ASCII(Names.ChID)-1 )
> > AND ChID > 'a'
> >
> > DROP TABLE Names
> >
> > How do you get _27_ rows btw?
> >
> > --
> > Jacco Schalkwijk MCDBA, MCSD, MCSE
> > Database Administrator
> > Eurostop Ltd.
> >
> >
> > "Henk Schreij" <henk@.schreijDOTnl> wrote in message
> > news:O$CUghtdDHA.1944@.TK2MSFTNGP12.phx.gbl...
> > > I have a table Names with two fields:
> > > ChID varchar(1)
> > > Name varchar(25)
> > > where the ChID char is in a range a .. z
> > > (there will never be more then 27 records)
> > > Some chars are used, some not
> > >
> > > Two examples:
> > > eg.
> > > I have 4 records: CharID b, c, e, and f are used
> > > I want to find the first not used char
> > > In this example it is the char a
> > > eg.
> > > I have 5 records: CharID a, b, c, e, and f are used
> > > I want to find the first not used char
> > > In this example it is the char d.
> > >
> > > Is a SELECT statement possible for finding the first not used Char?
> > > How would such a statement be?
> > >
> > >
> >
> >
>|||Sorry for the crosspost,
I also found out that the answer is not always correct.
(also thanks to news.rinet from russia)
I study this code to see if its better then the code I posted.
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> schreef in bericht
news:uyhXeT3dDHA.2816@.TK2MSFTNGP10.phx.gbl...
> Hi Henk,
> As pointed out in the other post it didn't work in all situations, but I
> have made the corrections in the code below.
> What the code now does is first check if there is a row that is 'a' (WHEN
> NOT EXISTS(SELECT NULL FROM Names n2 WHERE ChID = 'a') THEN 'a') , and if
> there isn't than that is of course the value you're looking for. This also
> catches the empty table. Next it will find the lowest ChID for which the
> next highest ChID in alphabetical order doesn't exists.
> You can also use a table variable with a-z in it and compare against that
> (second script)
> CREATE TABLE NAMES(ChID varchar(1), Name varchar(25) NULL)
> INSERT INTO Names(ChID) VALUES('a')
> INSERT INTO Names(ChID) VALUES('b')
> INSERT INTO Names(ChID) VALUES('c')
> INSERT INTO Names(ChID) VALUES('d')
> SELECT CASE WHEN NOT EXISTS(SELECT NULL FROM Names n2 WHERE ChID = 'a')
THEN
> 'a' ELSE
> MIN(CHAR(ASCII(ChID) + 1))
> END
> FROM Names
> WHERE
> NOT EXISTS(SELECT NULL FROM Names n2 WHERE ASCII(n2.ChID) => ASCII(Names.ChID)+1 )
> DROP TABLE Names
>
> CREATE TABLE NAMES(ChID varchar(1), Name varchar(25) NULL)
> INSERT INTO Names(ChID) VALUES('a')
> INSERT INTO Names(ChID) VALUES('b')
> INSERT INTO Names(ChID) VALUES('f')
> INSERT INTO Names(ChID) VALUES('d')
> DECLARE @.letters TABLE(letter char(1))
> DECLARE @.i TINYINT
> SET @.i = 97
> WHILE @.i < 123
> BEGIN
> INSERT INTO @.letters(letter) VALUES(CHAR(@.i))
> SET @.i = @.i +1
> END
> SELECT MIN(letter)
> FROM @.letters l
> LEFT OUTER JOIN Names n
> ON l.letter = n.ChID
> WHERE n.ChID IS NULL
>
> DROP TABLE Names
>
> --
> Jacco Schalkwijk MCDBA, MCSD, MCSE
> Database Administrator
> Eurostop Ltd.
>
> "Henk Schreij" <henk@.schreijDOTnl> wrote in message
> news:eIkC4dxdDHA.2168@.TK2MSFTNGP09.phx.gbl...
> > Jacco after studying your code, I must say it is not so simple as I
first
> > thought.
> > I do not understand the working of this piece of art yet, but I'am
trying.
> >
> > Oh, btw, a..z is 26 chars, not 27. I have to go to the primary school
> again
> > <g>.
> >
> > "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> schreef in bericht
> > news:ur2NovtdDHA.1448@.TK2MSFTNGP12.phx.gbl...
> >
> > > SELECT MIN(CHAR(ASCII(ChID) - 1)) FROM Names
> > > WHERE NOT EXISTS(SELECT NULL FROM Names n2 WHERE ASCII(n2.ChID) => > > ASCII(Names.ChID)-1 )
> > > AND ChID > 'a'
> > >
> > > How do you get _27_ rows btw?
> > >
> > > "Henk Schreij" <henk@.schreijDOTnl> wrote in message
> > > news:O$CUghtdDHA.1944@.TK2MSFTNGP12.phx.gbl...
> > > > I have a table Names with two fields:
> > > > ChID varchar(1)
> > > > Name varchar(25)
> > > > where the ChID char is in a range a .. z
> > > > (there will never be more then 27 records)
> > > > Some chars are used, some not
> > > >
> > > > Two examples:
> > > > eg.
> > > > I have 4 records: CharID b, c, e, and f are used
> > > > I want to find the first not used char
> > > > In this example it is the char a
> > > > eg.
> > > > I have 5 records: CharID a, b, c, e, and f are used
> > > > I want to find the first not used char
> > > > In this example it is the char d.
> > > >
> > > > Is a SELECT statement possible for finding the first not used Char?
> > > > How would such a statement be?
> >
> >
> >
>|||I'am sorry that I have to tell you that the sql statement did'nt give the
correct answer.
Example:
CREATE TABLE NAMES(ChID varchar(1), Name varchar(25) NULL)
INSERT INTO Names(ChID) VALUES('f')
INSERT INTO Names(ChID) VALUES('a')
INSERT INTO Names(ChID) VALUES('b')
INSERT INTO Names(ChID) VALUES('c')
SELECT MIN(CHAR(ASCII(ChID) - 1)) FROM Names
WHERE NOT EXISTS(SELECT NULL FROM Names n2
WHERE ASCII(n2.ChID) = ASCII(Names.ChID)-1 )
AND ChID > 'a'
DROP TABLE Names
The result is an 'e' in stead of 'd' (the first free char)
But you showed me a way to handle this problem (thanks for that).
I made this statement:
CREATE TABLE Names (ChID varchar(1), Name varchar(25) NULL)
INSERT INTO Names(ChID) VALUES('c')
INSERT INTO Names(ChID) VALUES('b')
INSERT INTO Names(ChID) VALUES('a')
INSERT INTO Names(ChID) VALUES('z')
SELECT ISNULL(MIN(Nw), 'a') FROM (
SELECT CHAR(MIN(ASCII(ChID) + 1)) AS Nw
FROM Names WHERE NOT (ASCII(ChID) + 1) IN (SELECT ASCII(ChID) FROM Names)
UNION
SELECT (CASE WHEN (MIN(ChID) >= 'b') THEN 'a' END) AS Nw FROM Names) Tmp
DROP TABLE Names
It gives the correct answers (the ISNULL is for a empty table)
but the code is not very well designed, using a UNION.
Is it possible to get the same result without a union?
Henk.
> > > "Henk Schreij" <henk@.schreijDOTnl> wrote in message
> > > news:O$CUghtdDHA.1944@.TK2MSFTNGP12.phx.gbl...
> > > > I have a table Names with two fields:
> > > > ChID varchar(1)
> > > > Name varchar(25)
> > > > where the ChID char is in a range a .. z
> > > > (there will never be more then 27 records)
> > > > Some chars are used, some not
> > > >
> > > > Two examples:
> > > > eg.
> > > > I have 4 records: CharID b, c, e, and f are used
> > > > I want to find the first not used char
> > > > In this example it is the char a
> > > > eg.
> > > > I have 5 records: CharID a, b, c, e, and f are used
> > > > I want to find the first not used char
> > > > In this example it is the char d.
> > > >
> > > > Is a SELECT statement possible for finding the first not used Char?
> > > > How would such a statement be?|||Hi Henk,
The WHERE clause you have posted here
WHERE NOT (ASCII(ChID) + 1) IN (SELECT ASCII(ChID) FROM Names)
is logically equivalent to the WHERE clause in my post in the other part of
this thread
WHERE NOT EXISTS(SELECT NULL FROM Names n2 WHERE ASCII(n2.ChID) =ASCII(Names.ChID)+1)
I know that using IN is easier to understand than using EXISTS, but I advise
you to try to understand the use of EXISTS, because IN is limited to an
equality on one column, where with EXISTS you can use multiple columns and
different operators (<, >, BETWEEN, etc). On top of that EXISTS will always
perform as least as well as IN, and often it performs a lot better.
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Henk Schreij" <henk@.schreijDOTnl> wrote in message
news:uDQfLY5dDHA.3584@.tk2msftngp13.phx.gbl...
> I made a syntesis of your and my statements:
> CREATE TABLE NAMES(ChID varchar(1), Name varchar(25) NULL)
> INSERT INTO Names(ChID) VALUES('d')
> INSERT INTO Names(ChID) VALUES('b')
> INSERT INTO Names(ChID) VALUES('c')
> INSERT INTO Names(ChID) VALUES('z')
> SELECT CASE
> WHEN NOT EXISTS (SELECT NULL FROM Names WHERE ChID = 'a') THEN 'a'
> ELSE CHAR(MIN(ASCII(ChID) + 1)) END
> FROM Names WHERE NOT (ASCII(ChID) + 1) IN (SELECT ASCII(ChID) FROM Names)
> I think this is the most elegant solution.
> Thanks a lot.
> Henk
> "Henk Schreij" <henk@.schreijDOTnl> schreef in bericht
> news:OPJslo4dDHA.3992@.TK2MSFTNGP11.phx.gbl...
> > I'am sorry that I have to tell you that the sql statement did'nt give
the
> > correct answer.
> >
> > Example:
> > CREATE TABLE NAMES(ChID varchar(1), Name varchar(25) NULL)
> > INSERT INTO Names(ChID) VALUES('f')
> > INSERT INTO Names(ChID) VALUES('a')
> > INSERT INTO Names(ChID) VALUES('b')
> > INSERT INTO Names(ChID) VALUES('c')
> >
> > SELECT MIN(CHAR(ASCII(ChID) - 1)) FROM Names
> > WHERE NOT EXISTS(SELECT NULL FROM Names n2
> > WHERE ASCII(n2.ChID) = ASCII(Names.ChID)-1 )
> > AND ChID > 'a'
> >
> > DROP TABLE Names
> >
> > The result is an 'e' in stead of 'd' (the first free char)
> >
> > But you showed me a way to handle this problem (thanks for that).
> >
> > I made this statement:
> > CREATE TABLE Names (ChID varchar(1), Name varchar(25) NULL)
> > INSERT INTO Names(ChID) VALUES('c')
> > INSERT INTO Names(ChID) VALUES('b')
> > INSERT INTO Names(ChID) VALUES('a')
> > INSERT INTO Names(ChID) VALUES('z')
> >
> > SELECT ISNULL(MIN(Nw), 'a') FROM (
> > SELECT CHAR(MIN(ASCII(ChID) + 1)) AS Nw
> > FROM Names WHERE NOT (ASCII(ChID) + 1) IN (SELECT ASCII(ChID) FROM
> Names)
> > UNION
> > SELECT (CASE WHEN (MIN(ChID) >= 'b') THEN 'a' END) AS Nw FROM Names)
Tmp
> >
> > DROP TABLE Names
> >
> > It gives the correct answers (the ISNULL is for a empty table)
> > but the code is not very well designed, using a UNION.
> >
> > Is it possible to get the same result without a union?
> >
> > Henk.
> > > > > "Henk Schreij" <henk@.schreijDOTnl> wrote in message
> > > > > news:O$CUghtdDHA.1944@.TK2MSFTNGP12.phx.gbl...
> > > > > > I have a table Names with two fields:
> > > > > > ChID varchar(1)
> > > > > > Name varchar(25)
> > > > > > where the ChID char is in a range a .. z
> > > > > > (there will never be more then 27 records)
> > > > > > Some chars are used, some not
> > > > > >
> > > > > > Two examples:
> > > > > > eg.
> > > > > > I have 4 records: CharID b, c, e, and f are used
> > > > > > I want to find the first not used char
> > > > > > In this example it is the char a
> > > > > > eg.
> > > > > > I have 5 records: CharID a, b, c, e, and f are used
> > > > > > I want to find the first not used char
> > > > > > In this example it is the char d.
> > > > > >
> > > > > > Is a SELECT statement possible for finding the first not used
> Char?
> > > > > > How would such a statement be?
>
>|||Jacco, thank you for this extra explanation.
I'll study the Exists in my SQL-book and try to use it more often, as it has
indeed some benefits over the IN statement.
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> schreef in bericht
news:#8QRez5dDHA.1460@.TK2MSFTNGP10.phx.gbl...
> Hi Henk,
> The WHERE clause you have posted here
> WHERE NOT (ASCII(ChID) + 1) IN (SELECT ASCII(ChID) FROM Names)
> is logically equivalent to the WHERE clause in my post in the other part
of
> this thread
> WHERE NOT EXISTS(SELECT NULL FROM Names n2 WHERE ASCII(n2.ChID) => ASCII(Names.ChID)+1)
> I know that using IN is easier to understand than using EXISTS, but I
advise
> you to try to understand the use of EXISTS, because IN is limited to an
> equality on one column, where with EXISTS you can use multiple columns and
> different operators (<, >, BETWEEN, etc). On top of that EXISTS will
always
> perform as least as well as IN, and often it performs a lot better.
> --
> Jacco Schalkwijk MCDBA, MCSD, MCSE
> Database Administrator
> Eurostop Ltd.
>
> "Henk Schreij" <henk@.schreijDOTnl> wrote in message
> news:uDQfLY5dDHA.3584@.tk2msftngp13.phx.gbl...
> > I made a syntesis of your and my statements:
> > CREATE TABLE NAMES(ChID varchar(1), Name varchar(25) NULL)
> > INSERT INTO Names(ChID) VALUES('d')
> > INSERT INTO Names(ChID) VALUES('b')
> > INSERT INTO Names(ChID) VALUES('c')
> > INSERT INTO Names(ChID) VALUES('z')
> >
> > SELECT CASE
> > WHEN NOT EXISTS (SELECT NULL FROM Names WHERE ChID = 'a') THEN 'a'
> > ELSE CHAR(MIN(ASCII(ChID) + 1)) END
> > FROM Names WHERE NOT (ASCII(ChID) + 1) IN (SELECT ASCII(ChID) FROM
Names)
> >
> > I think this is the most elegant solution.
> > Thanks a lot.
> > Henk
> >
> > "Henk Schreij" <henk@.schreijDOTnl> schreef in bericht
> > news:OPJslo4dDHA.3992@.TK2MSFTNGP11.phx.gbl...
> > > I'am sorry that I have to tell you that the sql statement did'nt give
> the
> > > correct answer.
> > >
> > > Example:
> > > CREATE TABLE NAMES(ChID varchar(1), Name varchar(25) NULL)
> > > INSERT INTO Names(ChID) VALUES('f')
> > > INSERT INTO Names(ChID) VALUES('a')
> > > INSERT INTO Names(ChID) VALUES('b')
> > > INSERT INTO Names(ChID) VALUES('c')
> > >
> > > SELECT MIN(CHAR(ASCII(ChID) - 1)) FROM Names
> > > WHERE NOT EXISTS(SELECT NULL FROM Names n2
> > > WHERE ASCII(n2.ChID) = ASCII(Names.ChID)-1 )
> > > AND ChID > 'a'
> > >
> > > DROP TABLE Names
> > >
> > > The result is an 'e' in stead of 'd' (the first free char)
> > >
> > > But you showed me a way to handle this problem (thanks for that).
> > >
> > > I made this statement:
> > > CREATE TABLE Names (ChID varchar(1), Name varchar(25) NULL)
> > > INSERT INTO Names(ChID) VALUES('c')
> > > INSERT INTO Names(ChID) VALUES('b')
> > > INSERT INTO Names(ChID) VALUES('a')
> > > INSERT INTO Names(ChID) VALUES('z')
> > >
> > > SELECT ISNULL(MIN(Nw), 'a') FROM (
> > > SELECT CHAR(MIN(ASCII(ChID) + 1)) AS Nw
> > > FROM Names WHERE NOT (ASCII(ChID) + 1) IN (SELECT ASCII(ChID) FROM
> > Names)
> > > UNION
> > > SELECT (CASE WHEN (MIN(ChID) >= 'b') THEN 'a' END) AS Nw FROM Names)
> Tmp
> > >
> > > DROP TABLE Names
> > >
> > > It gives the correct answers (the ISNULL is for a empty table)
> > > but the code is not very well designed, using a UNION.
> > >
> > > Is it possible to get the same result without a union?
> > >
> > > Henk.
> > > > > > "Henk Schreij" <henk@.schreijDOTnl> wrote in message
> > > > > > news:O$CUghtdDHA.1944@.TK2MSFTNGP12.phx.gbl...
> > > > > > > I have a table Names with two fields:
> > > > > > > ChID varchar(1)
> > > > > > > Name varchar(25)
> > > > > > > where the ChID char is in a range a .. z
> > > > > > > (there will never be more then 27 records)
> > > > > > > Some chars are used, some not
> > > > > > >
> > > > > > > Two examples:
> > > > > > > eg.
> > > > > > > I have 4 records: CharID b, c, e, and f are used
> > > > > > > I want to find the first not used char
> > > > > > > In this example it is the char a
> > > > > > > eg.
> > > > > > > I have 5 records: CharID a, b, c, e, and f are used
> > > > > > > I want to find the first not used char
> > > > > > > In this example it is the char d.
> > > > > > >
> > > > > > > Is a SELECT statement possible for finding the first not used
> > Char?
> > > > > > > How would such a statement be?
> >
> >
> >
>