very simple but hard to understand

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

very simple but hard to understand

Author

Topic

morfious
Starting Member

2 Posts

Posted - 2011-01-26 : 16:44:56

i'm newbie

just wanna know if i creat a column has data

akash
anurag
rahul
jaiswal
tripathi

now if i perform a query

select column_name from table where column >'a'

so why it shows values that contnt 'A' alphabate

it should not show akash and anurag

if i perform a query

select column_name from table where column_name >'r'

then answer will

rahul
tripathi

My ques is i'm not using greater than equal(>=) to sign so why its including that R alphabate it should leave rahul and should show tripathi only as it did for akash jaiswal and anurag

please remove my confussion

Defeat your enemies by your success

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2011-01-26 : 17:04:41

Your query compares to the first character only, not the entire string. If you were to specify column > 'akash' it would return your data starting from 'anurag'.

If you want to compare just to the first character, you could do something like this:

SELECT column_name
FROM table
WHERE substring(column_name, 1, 1) > 'a'

Some days you're the dog, and some days you're the fire hydrant.

morfious
Starting Member

2 Posts

Posted - 2011-01-27 : 14:01:39

definatly it compares first letter 'a' so here a < r so its should not show akash and anurag .. only should show rahul jaiswal and tripathi coz r,j, and t is greater than 'a'.....if i use equal to sign then it should show akash and anurag both with rest of words is'nt it ??

select column_name from table where column >'a'

Defeat your enemies by your success

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-01-27 : 14:08:28

quote:
Originally posted by morfious

definatly it compares first letter 'a' so here a < r so its should not show akash and anurag .. only should show rahul jaiswal and tripathi coz r,j, and t is greater than 'a'.....if i use equal to sign then it should show akash and anurag both with rest of words is'nt it ??

select column_name from table where column >'a'

Defeat your enemies by your success

You are comparing the string 'a' to other string 'akash' (for example). Is 'akash' less than 'a'? No, it is not. Thus why your query is not working they way you think/want it to. If you only want to compare the fist character then you need to tell SQL to do that. One way is to do as Skorch has suggested.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-01-27 : 14:12:17

SELECT column_name
FROM table
WHERE column_name like 'a%'

Jim

Everyday I learn something that somebody else already knew

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-01-27 : 14:12:26

Maybe this will help? If you cast the strings to BIANRY youc an see one possible representation of their values. WHen you can compare those and see that A is less than AKASH:

SELECT 
    CAST('a' AS BINARY(10)),
    CAST('akash' AS BINARY(10)),
    CASE
        WHEN CAST('a' AS BINARY(10)) < CAST('akash' AS BINARY(10))
            THEN 'a is less than akash'
        ELSE 'akash is less than a'
    END

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-01-27 : 14:14:02

quote:
Originally posted by jimf

SELECT column_name
FROM table
WHERE column_name like 'a%'

Jim

Everyday I learn something that somebody else already knew

I'm a little confused,but I think the OP is looking for: NOT LIKE 'a%'

Again not sure.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-27 : 14:38:35

Why not just change "select column_name from table where column > 'a'"
to "select column_name from table where column >= 'b'"

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources