SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Integer Suggestions
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

oldman0215
Starting Member

5 Posts

Posted - 05/25/2007 :  13:32:58  Show Profile  Reply with Quote
A little background:
I am trying to provide a feature where we offer the available values of a certain attribute in our database. This feature will take into account what the user has already typed in the text box when offering suggestions.

Now for the SQL question.
If the user has typed in 1 and clicked the button to retrieve available values and the field in the database is numeric,
is there a builtin way to easily say I want all numeric values that start with 1 (ex. 1, 10, 1234)?

Kristen
Test

United Kingdom
22403 Posts

Posted - 05/25/2007 :  13:36:43  Show Profile  Reply with Quote
WHERE CONVERT(varchar(20), MyColumn) LIKE '1%'

but probably not very efficient!

Kristen
Go to Top of Page

oldman0215
Starting Member

5 Posts

Posted - 05/25/2007 :  14:03:30  Show Profile  Reply with Quote
Yeah, I thought of that but with a very good chance that there are a lot of values I think that query would die. There is also another instance I think it might not work. When the number is something like 0.00000000000002345. I think there is a chance that this number would be converted to scientific notation, which would mess up the like condition in certain circumstances. I might be wrong about that though.
Go to Top of Page

oldman0215
Starting Member

5 Posts

Posted - 05/25/2007 :  14:11:11  Show Profile  Reply with Quote
Is there a numeric way of doings this?
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 05/25/2007 :  14:12:56  Show Profile  Visit spirit1's Homepage  Reply with Quote

CREATE TABLE #temp (id numeric(20, 12), NAME VARCHAR(50))
INSERT INTO #temp 
SELECT 0.00000000000002345, 'gbks' UNION ALL
SELECT 5, 'bdrgrs' UNION ALL
SELECT 1234, 'bvrs' UNION ALL
SELECT 125, 'esgs' UNION ALL
SELECT 33, 'htrew' UNION ALL
SELECT 33568, 'bdfs' UNION ALL
SELECT 5526, 'z43wzhg4' UNION ALL
SELECT 899, 'gdsgd' UNION ALL
SELECT 110055.4343252, 'hdysgs'

SELECT *
FROM #temp
WHERE id LIKE '5%'

GO
DROP table #temp


_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

oldman0215
Starting Member

5 Posts

Posted - 05/25/2007 :  14:18:23  Show Profile  Reply with Quote
Spirit,

Are you saying that like works on numeric columns? That would be awsome but everything I have read says it is only used on columns containing character data. I'll give it a try.

Thanks
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 05/25/2007 :  14:19:57  Show Profile  Visit spirit1's Homepage  Reply with Quote
yes it also works on numerics because they can be implicitly comverted between datatypes.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Edited by - spirit1 on 05/25/2007 14:20:31
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 05/25/2007 :  14:20:21  Show Profile  Reply with Quote
"lot of values"

You could do a TOP 100 (say) to give a short-ish list. Or you use COUNT(*) and give NO results until the COUNT(*) was under, say, 100.

"When the number is something like 0.00000000000002345"

From your intiial example I took it to be INT. If its FLOAT just make sure that the CONVERT (or similar) will output in a format that the LIKE will latch onto properly.

Alternatively duplciate the Numeric column into a Varchar column (with appropriate formatting) and uis that for the LIKE test. If you INDEX that column it will work nice and quickly (although you'll still need something to prevent you returnning shed-loads of values after only 1 character has been typed!)

Kristen
Go to Top of Page

oldman0215
Starting Member

5 Posts

Posted - 05/25/2007 :  14:28:30  Show Profile  Reply with Quote
Spirit,

It does work! You are right. I should have just tried it before asking instead of believing everything I read.

Thanks for everybody's help!
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 05/25/2007 :  14:30:17  Show Profile  Visit spirit1's Homepage  Reply with Quote
well you'll know next time to try first

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 05/25/2007 :  14:33:08  Show Profile  Reply with Quote
Not sure I would be comfortable with an IMplicit conversion ... why would it work IMplicit and not EXplicit? (or am I just being Thick?!)
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 05/25/2007 :  14:34:17  Show Profile  Visit spirit1's Homepage  Reply with Quote
explicit conversion is when you do a convert.
implicit is when you don't.

so what aren't you comfie with?
maybe i should send you some nice thick pillows

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Edited by - spirit1 on 05/25/2007 14:34:54
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 05/25/2007 :  14:39:34  Show Profile  Reply with Quote
Hehehe .. I was thinking of the thing in SQL2000 SP4 where cross-datatype JOIN and WHERE comparisons caused perform to go to hell-in-a-hamper on some IMplicit conversions.

I hate it when I am relying on a side-effect (although I may not realise, and it "just works", which is even worse because it might NOT "just work" in some future version ) ... which all leads to me preferring to SHOW that I realise that a conversion is required in an operation - i.e. by CASTing (or CONVERTing) the values EXplicitly.

I **long** for a SQL-Lint tool ...

If you can't sleep tonight Spirit maybe you could knock one up for me?!

Kristen
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 05/25/2007 :  14:46:52  Show Profile  Visit spirit1's Homepage  Reply with Quote
> SQL-Lint tool"

what's that???

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 05/26/2007 :  04:11:18  Show Profile  Reply with Quote
"Lint" is a tool that "picks fluff" off a program. Originally used for C because there are lots of gotchas in C. For example:

if (A = B) ...

A = B is an assignment, but it is allowed as a logic statement. The assignment takes place, and if the value assigned is 0 its falsy, otherwise its truthy.

A comparison would be

if (A == B) ...

so Lint will alert you to that. Also implicit conversion, use of defined variable before assignment, check that your indentation matches the logic, and so on.

There is a GREAT JavaScript Lint http://www.jslint.com/ - in fact if you use Javascript at all its worth watching the author's (Douglas Crockford) videos on JavaScript and The DOM - he now works for Yahoo - on the YUI project I think.

Kristen
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000