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