Order By statement name begining with A

Hello

Can anybody tell me the correct MS SQL statement to display names: Where each name displayed is starting with a certain letter of the alphabet only. For example:

The list should show names starting with the letter A

(what must I change in my statement below)

SELECT LastN, FirstN, Tel
FROM People_DB
WHERE LastN = 'A'
ORDER BY LastN ASC

Last Name

Adams,

Andrews,

Archer,

Arrow

Thanks

Lynn

Use the LIKE operator with the % wildcard, like this:

SELECT LastN, FirstN, Tel
FROM People_DB
WHERE LastNLIKE 'A%'
ORDER BY LastN ASC

You can also use an underscore to match a single character. Check out the LIKE clause in BOL for all the options.

Don

|||

Hi Don

Thanks for the speedy reply and the information.

Lynn

Order a character field numercially

I have a table with code and description columns. The code column is a CHAR
(10) with values like:
1, 101, 15, DC1, etc.
This code is displayed in a drop down list and sorts as follows because it i
s a character field:
1
101
15
DC1
I need to have this drop down sorted in a manner that takes into account the
numeric order of the code, as follows:
1
15
101
DC1
The codes are industry standard codes, so I cannot tell users to enter 001,
015, 101, etc. The codes are alphanumeric in nature. There are hundreds of
codes, which makes it difficult for a user to scroll down to find the corre
ct code. Is there a way to
sort a character field in this manner? ThanksORDER BY CASE ISNUMERIC(column) WHEN 1 THEN CONVERT(INT, column) ELSE column
END
( Except note that there are some isNumeric exceptions...
http://www.aspfaq.com/show.asp?id=2390 )
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Don Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:BC13D832-F8E6-4519-AB33-E67034971D27@.microsoft.com...
> I have a table with code and description columns. The code column is a
CHAR(10) with values like:
> 1, 101, 15, DC1, etc.
> This code is displayed in a drop down list and sorts as follows because it
is a character field:
> 1
> 101
> 15
> DC1
> I need to have this drop down sorted in a manner that takes into account
the numeric order of the code, as follows:
> 1
> 15
> 101
> DC1
> The codes are industry standard codes, so I cannot tell users to enter
001, 015, 101, etc. The codes are alphanumeric in nature. There are
hundreds of codes, which makes it difficult for a user to scroll down to
find the correct code. Is there a way to sort a character field in this
manner? Thanks
>|||you could try:
ORDER BY
CASE PATINDEX('[a-z]', LEFT(YourColumn, 1))
WHEN 0 THEN RIGHT(REPLICATE('0', 10) + RTRIM(YourColumn), 10)
ELSE YourColumn
END
This assumes that all codes with alphas begin with an alpha. If that's not
true, change it to:
ORDER BY
CASE PATINDEX('%[a-z]%', YourColumn)
WHEN 0 THEN RIGHT(REPLICATE('0', 10) + RTRIM(YourColumn), 10)
ELSE YourColumn
END
You also may have to compensate for a case-sensitive collation setting, and
possibly other issues... But this is a start, at least.
"Don Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:BC13D832-F8E6-4519-AB33-E67034971D27@.microsoft.com...
> I have a table with code and description columns. The code column is a
CHAR(10) with values like:
> 1, 101, 15, DC1, etc.
> This code is displayed in a drop down list and sorts as follows because it
is a character field:
> 1
> 101
> 15
> DC1
> I need to have this drop down sorted in a manner that takes into account
the numeric order of the code, as follows:
> 1
> 15
> 101
> DC1
> The codes are industry standard codes, so I cannot tell users to enter
001, 015, 101, etc. The codes are alphanumeric in nature. There are
hundreds of codes, which makes it difficult for a user to scroll down to
find the correct code. Is there a way to sort a character field in this
manner? Thanks
>|||Don
CREATE TABLE TEST
(
COL VARCHAR(4)
)
GO
INSERT INTO TEST VALUES ('1')
INSERT INTO TEST VALUES ('101')
INSERT INTO TEST VALUES ('15')
INSERT INTO TEST VALUES ('DC1')
GO
SELECT * FROM TEST ORDER BY RIGHT('0000'+COL,4)
GO
DROP TABLE TEST
"Don Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:BC13D832-F8E6-4519-AB33-E67034971D27@.microsoft.com...
> I have a table with code and description columns. The code column is a
CHAR(10) with values like:
> 1, 101, 15, DC1, etc.
> This code is displayed in a drop down list and sorts as follows because it
is a character field:
> 1
> 101
> 15
> DC1
> I need to have this drop down sorted in a manner that takes into account
the numeric order of the code, as follows:
> 1
> 15
> 101
> DC1
> The codes are industry standard codes, so I cannot tell users to enter
001, 015, 101, etc. The codes are alphanumeric in nature. There are
hundreds of codes, which makes it difficult for a user to scroll down to
find the correct code. Is there a way to sort a character field in this
manner? Thanks
>|||Yet another solution:
ORDER BY LEN(YourColumn), YourColumn
Hope this helps,
Gert-jan
Don Jones wrote:
> I have a table with code and description columns. The code column is a CH
AR(10) with values like:
> 1, 101, 15, DC1, etc.
> This code is displayed in a drop down list and sorts as follows because it
is a character field:
> 1
> 101
> 15
> DC1
> I need to have this drop down sorted in a manner that takes into account t
he numeric order of the code, as follows:
> 1
> 15
> 101
> DC1
> The codes are industry standard codes, so I cannot tell users to enter 001, 015, 1
01, etc. The codes are alphanumeric in nature. There are hundreds of codes, which
makes it difficult for a user to scroll down to find the correct code. Is there a w
ay
to sort a character field in this manner? Thanks
(Please reply only to the newsgroup)

Order a character field numercially

I have a table with code and description columns. The code column is a CHAR(10) with values like:
1, 101, 15, DC1, etc.
This code is displayed in a drop down list and sorts as follows because it is a character field:
1
101
15
DC1
I need to have this drop down sorted in a manner that takes into account the numeric order of the code, as follows:
1
15
101
DC1
The codes are industry standard codes, so I cannot tell users to enter 001, 015, 101, etc. The codes are alphanumeric in nature. There are hundreds of codes, which makes it difficult for a user to scroll down to find the correct code. Is there a way to
sort a character field in this manner? Thanks
ORDER BY CASE ISNUMERIC(column) WHEN 1 THEN CONVERT(INT, column) ELSE column
END
( Except note that there are some isNumeric exceptions...
http://www.aspfaq.com/show.asp?id=2390 )
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Don Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:BC13D832-F8E6-4519-AB33-E67034971D27@.microsoft.com...
> I have a table with code and description columns. The code column is a
CHAR(10) with values like:
> 1, 101, 15, DC1, etc.
> This code is displayed in a drop down list and sorts as follows because it
is a character field:
> 1
> 101
> 15
> DC1
> I need to have this drop down sorted in a manner that takes into account
the numeric order of the code, as follows:
> 1
> 15
> 101
> DC1
> The codes are industry standard codes, so I cannot tell users to enter
001, 015, 101, etc. The codes are alphanumeric in nature. There are
hundreds of codes, which makes it difficult for a user to scroll down to
find the correct code. Is there a way to sort a character field in this
manner? Thanks
>
|||you could try:
ORDER BY
CASE PATINDEX('[a-z]', LEFT(YourColumn, 1))
WHEN 0 THEN RIGHT(REPLICATE('0', 10) + RTRIM(YourColumn), 10)
ELSE YourColumn
END
This assumes that all codes with alphas begin with an alpha. If that's not
true, change it to:
ORDER BY
CASE PATINDEX('%[a-z]%', YourColumn)
WHEN 0 THEN RIGHT(REPLICATE('0', 10) + RTRIM(YourColumn), 10)
ELSE YourColumn
END
You also may have to compensate for a case-sensitive collation setting, and
possibly other issues... But this is a start, at least.
"Don Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:BC13D832-F8E6-4519-AB33-E67034971D27@.microsoft.com...
> I have a table with code and description columns. The code column is a
CHAR(10) with values like:
> 1, 101, 15, DC1, etc.
> This code is displayed in a drop down list and sorts as follows because it
is a character field:
> 1
> 101
> 15
> DC1
> I need to have this drop down sorted in a manner that takes into account
the numeric order of the code, as follows:
> 1
> 15
> 101
> DC1
> The codes are industry standard codes, so I cannot tell users to enter
001, 015, 101, etc. The codes are alphanumeric in nature. There are
hundreds of codes, which makes it difficult for a user to scroll down to
find the correct code. Is there a way to sort a character field in this
manner? Thanks
>
|||Don
CREATE TABLE TEST
(
COL VARCHAR(4)
)
GO
INSERT INTO TEST VALUES ('1')
INSERT INTO TEST VALUES ('101')
INSERT INTO TEST VALUES ('15')
INSERT INTO TEST VALUES ('DC1')
GO
SELECT * FROM TEST ORDER BY RIGHT('0000'+COL,4)
GO
DROP TABLE TEST
"Don Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:BC13D832-F8E6-4519-AB33-E67034971D27@.microsoft.com...
> I have a table with code and description columns. The code column is a
CHAR(10) with values like:
> 1, 101, 15, DC1, etc.
> This code is displayed in a drop down list and sorts as follows because it
is a character field:
> 1
> 101
> 15
> DC1
> I need to have this drop down sorted in a manner that takes into account
the numeric order of the code, as follows:
> 1
> 15
> 101
> DC1
> The codes are industry standard codes, so I cannot tell users to enter
001, 015, 101, etc. The codes are alphanumeric in nature. There are
hundreds of codes, which makes it difficult for a user to scroll down to
find the correct code. Is there a way to sort a character field in this
manner? Thanks
>
|||Yet another solution:
ORDER BY LEN(YourColumn), YourColumn
Hope this helps,
Gert-jan
Don Jones wrote:
> I have a table with code and description columns. The code column is a CHAR(10) with values like:
> 1, 101, 15, DC1, etc.
> This code is displayed in a drop down list and sorts as follows because it is a character field:
> 1
> 101
> 15
> DC1
> I need to have this drop down sorted in a manner that takes into account the numeric order of the code, as follows:
> 1
> 15
> 101
> DC1
> The codes are industry standard codes, so I cannot tell users to enter 001, 015, 101, etc. The codes are alphanumeric in nature. There are hundreds of codes, which makes it difficult for a user to scroll down to find the correct code. Is there a way
to sort a character field in this manner? Thanks
(Please reply only to the newsgroup)

Order a character field numercially

I have a table with code and description columns. The code column is a CHAR(10) with values like
1, 101, 15, DC1, etc
This code is displayed in a drop down list and sorts as follows because it is a character field
10
1
DC
I need to have this drop down sorted in a manner that takes into account the numeric order of the code, as follows
1
10
DC
The codes are industry standard codes, so I cannot tell users to enter 001, 015, 101, etc. The codes are alphanumeric in nature. There are hundreds of codes, which makes it difficult for a user to scroll down to find the correct code. Is there a way to sort a character field in this manner? ThankORDER BY CASE ISNUMERIC(column) WHEN 1 THEN CONVERT(INT, column) ELSE column
END
( Except note that there are some isNumeric exceptions...
http://www.aspfaq.com/show.asp?id=2390 )
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Don Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:BC13D832-F8E6-4519-AB33-E67034971D27@.microsoft.com...
> I have a table with code and description columns. The code column is a
CHAR(10) with values like:
> 1, 101, 15, DC1, etc.
> This code is displayed in a drop down list and sorts as follows because it
is a character field:
> 1
> 101
> 15
> DC1
> I need to have this drop down sorted in a manner that takes into account
the numeric order of the code, as follows:
> 1
> 15
> 101
> DC1
> The codes are industry standard codes, so I cannot tell users to enter
001, 015, 101, etc. The codes are alphanumeric in nature. There are
hundreds of codes, which makes it difficult for a user to scroll down to
find the correct code. Is there a way to sort a character field in this
manner? Thanks
>|||you could try:
ORDER BY
CASE PATINDEX('[a-z]', LEFT(YourColumn, 1))
WHEN 0 THEN RIGHT(REPLICATE('0', 10) + RTRIM(YourColumn), 10)
ELSE YourColumn
END
This assumes that all codes with alphas begin with an alpha. If that's not
true, change it to:
ORDER BY
CASE PATINDEX('%[a-z]%', YourColumn)
WHEN 0 THEN RIGHT(REPLICATE('0', 10) + RTRIM(YourColumn), 10)
ELSE YourColumn
END
You also may have to compensate for a case-sensitive collation setting, and
possibly other issues... But this is a start, at least.
"Don Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:BC13D832-F8E6-4519-AB33-E67034971D27@.microsoft.com...
> I have a table with code and description columns. The code column is a
CHAR(10) with values like:
> 1, 101, 15, DC1, etc.
> This code is displayed in a drop down list and sorts as follows because it
is a character field:
> 1
> 101
> 15
> DC1
> I need to have this drop down sorted in a manner that takes into account
the numeric order of the code, as follows:
> 1
> 15
> 101
> DC1
> The codes are industry standard codes, so I cannot tell users to enter
001, 015, 101, etc. The codes are alphanumeric in nature. There are
hundreds of codes, which makes it difficult for a user to scroll down to
find the correct code. Is there a way to sort a character field in this
manner? Thanks
>|||Don
CREATE TABLE TEST
(
COL VARCHAR(4)
)
GO
INSERT INTO TEST VALUES ('1')
INSERT INTO TEST VALUES ('101')
INSERT INTO TEST VALUES ('15')
INSERT INTO TEST VALUES ('DC1')
GO
SELECT * FROM TEST ORDER BY RIGHT('0000'+COL,4)
GO
DROP TABLE TEST
"Don Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:BC13D832-F8E6-4519-AB33-E67034971D27@.microsoft.com...
> I have a table with code and description columns. The code column is a
CHAR(10) with values like:
> 1, 101, 15, DC1, etc.
> This code is displayed in a drop down list and sorts as follows because it
is a character field:
> 1
> 101
> 15
> DC1
> I need to have this drop down sorted in a manner that takes into account
the numeric order of the code, as follows:
> 1
> 15
> 101
> DC1
> The codes are industry standard codes, so I cannot tell users to enter
001, 015, 101, etc. The codes are alphanumeric in nature. There are
hundreds of codes, which makes it difficult for a user to scroll down to
find the correct code. Is there a way to sort a character field in this
manner? Thanks
>|||Yet another solution:
ORDER BY LEN(YourColumn), YourColumn
Hope this helps,
Gert-jan
Don Jones wrote:
> I have a table with code and description columns. The code column is a CHAR(10) with values like:
> 1, 101, 15, DC1, etc.
> This code is displayed in a drop down list and sorts as follows because it is a character field:
> 1
> 101
> 15
> DC1
> I need to have this drop down sorted in a manner that takes into account the numeric order of the code, as follows:
> 1
> 15
> 101
> DC1
> The codes are industry standard codes, so I cannot tell users to enter 001, 015, 101, etc. The codes are alphanumeric in nature. There are hundreds of codes, which makes it difficult for a user to scroll down to find the correct code. Is there a way to sort a character field in this manner? Thanks
--
(Please reply only to the newsgroup)

Oracle SQLPlus Worksheet

When I run an SQLPlus query, the displayed width of each column is the width of the data item. This means that the column header for many fields is truncated. How do I override this default width in order to make the entire column header appear without being truncated. An example is below. The last column is titled DESTINATION, but only DES shows.

BAG_TAG_N ORI DES
--- -- --
3UA233468 TUL ORD
3UA233468 AVP ORD
3UA233468 ORD MLIOriginally posted by mryan916
When I run an SQLPlus query, the displayed width of each column is the width of the data item. This means that the column header for many fields is truncated. How do I override this default width in order to make the entire column header appear without being truncated. An example is below. The last column is titled DESTINATION, but only DES shows.

BAG_TAG_N ORI DES
--- -- --
3UA233468 TUL ORD
3UA233468 AVP ORD
3UA233468 ORD MLI

try: set linesize 120