| Author |
Topic |
|
oldman0215
Starting Member
5 Posts |
 Posted - 2007-05-25 : 13:32:58
|
| 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
22859 Posts |
Posted - 2007-05-25 : 13:36:43
|
| WHERE CONVERT(varchar(20), MyColumn) LIKE '1%'but probably not very efficient!Kristen |
 |
|
|
oldman0215
Starting Member
5 Posts |
Posted - 2007-05-25 : 14:03:30
|
| 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. |
|
|
|
oldman0215
Starting Member
5 Posts |
Posted - 2007-05-25 : 14:11:11
|
| Is there a numeric way of doings this? |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-05-25 : 14:12:56
|
| [code]CREATE TABLE #temp (id numeric(20, 12), NAME VARCHAR(50))INSERT INTO #temp SELECT 0.00000000000002345, 'gbks' UNION ALLSELECT 5, 'bdrgrs' UNION ALLSELECT 1234, 'bvrs' UNION ALLSELECT 125, 'esgs' UNION ALLSELECT 33, 'htrew' UNION ALLSELECT 33568, 'bdfs' UNION ALLSELECT 5526, 'z43wzhg4' UNION ALLSELECT 899, 'gdsgd' UNION ALLSELECT 110055.4343252, 'hdysgs'SELECT *FROM #tempWHERE id LIKE '5%'GODROP table #temp[/code]_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
oldman0215
Starting Member
5 Posts |
Posted - 2007-05-25 : 14:18:23
|
| 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 |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-05-25 : 14:19:57
|
| yes it also works on numerics because they can be implicitly comverted between datatypes._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-25 : 14:20:21
|
| "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 |
|
|
|
oldman0215
Starting Member
5 Posts |
Posted - 2007-05-25 : 14:28:30
|
| 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! |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-05-25 : 14:30:17
|
well you'll know next time to try first  _______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-25 : 14:33:08
|
| Not sure I would be comfortable with an IMplicit conversion ... why would it work IMplicit and not EXplicit? (or am I just being Thick?!) |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-05-25 : 14:34:17
|
| 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 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-25 : 14:39:34
|
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 |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-05-25 : 14:46:52
|
| > SQL-Lint tool"what's that???_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-26 : 04:11:18
|
| "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 |
|
|
|
|
Integer Suggestions