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 2012 Forums
 Transact-SQL (2012)
 sql statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ntn104
Posting Yak Master

175 Posts

Posted - 03/10/2014 :  12:39:11  Show Profile  Reply with Quote
hello,

How do I write sql statement to select only those records that displayed 9 digits of ID #. Because the table showed some like 1, 2012....198...ect...but I wanted to display only ID = 9 digits.

Please advise,

select id
from table
where id <>''
and id <>'000000000'


Thanks,

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 03/10/2014 :  12:47:25  Show Profile  Reply with Quote
Did you mean you want to display prefixed zero's if there are fewer than 9 digits in the ID?
SELECT RIGHT('000000000'+CAST(id AS VARCHAR(16)),9) FROM .....
Go to Top of Page

ntn104
Posting Yak Master

175 Posts

Posted - 03/10/2014 :  13:46:45  Show Profile  Reply with Quote
No, I wanted to get result of those accounts that have id = 9 digits only, not with less than 9 digits.

For example, if ID = 123, or ID=2012 ---> DO NOT SELECT/DISPLAY
if ID = 9 digits such as ID =300456789, then display as result.

I think I may have to put substr(id,1,9) on a select statement.
Thanks,


quote:
Originally posted by James K

Did you mean you want to display prefixed zero's if there are fewer than 9 digits in the ID?
SELECT RIGHT('000000000'+CAST(id AS VARCHAR(16)),9) FROM .....



Edited by - ntn104 on 03/10/2014 14:58:36
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 03/10/2014 :  15:17:38  Show Profile  Reply with Quote
If the data type of id column is numeric type, add a where clause that is like this:
WHERE
	id >= 100000000
	AND id <= 999999999
If it is a character column, add a where clause like this:
WHERE
	LEN(id) = 9
Go to Top of Page

ntn104
Posting Yak Master

175 Posts

Posted - 03/11/2014 :  08:13:24  Show Profile  Reply with Quote
The ID is character, I did use len(id)=9 in where clause, but still not working...not sure why.. Thanks,

quote:
Originally posted by James K

If the data type of id column is numeric type, add a where clause that is like this:
WHERE
	id >= 100000000
	AND id <= 999999999
If it is a character column, add a where clause like this:
WHERE
	LEN(id) = 9


Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 03/11/2014 :  08:28:58  Show Profile  Reply with Quote
Can you give some more information? When you said it is not working, what did you mean? Is it giving you a syntax error, or no results, or incorrect results, or something else?

If you post the question with examples where it does not work, that would make it easier to respond. See here: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

ntn104
Posting Yak Master

175 Posts

Posted - 03/11/2014 :  08:57:18  Show Profile  Reply with Quote
I guess I find out why the len function not working since the field ID set up for 9 character, but those invalid IDs are in the middle of the field (within the 9 characters allowance). Please see some example below:


1
00
16
74
99
127
164
172
202
202
291
889
918
0004
0012
0022
0022
0088
0105
0132
0132
0181
0236
0260
0292
0292
0350
0377
0377
0411
0412
0450
0546
0571
0594
0631
1043419
1142420
1142420



I tried this where id <>' 1 ' (to give spaces in front of the #1 and after the #1 enough for 9 characters)
and it works, however we can't do for all the scenarios...> thousand times ....I am trying to figure out the trim method on left and right...but not sure yet...

Thanks,


quote:
Originally posted by James K

Can you give some more information? When you said it is not working, what did you mean? Is it giving you a syntax error, or no results, or incorrect results, or something else?

If you post the question with examples where it does not work, that would make it easier to respond. See here: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Edited by - ntn104 on 03/11/2014 09:06:51
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 03/11/2014 :  10:00:31  Show Profile  Reply with Quote
Replace function might help you in this case...

e.g. Len(Replace(ColumnName,' ',''))=9

And if there are non numeric characters others than spaces and your requirements are only to look for numeric with the length of 9 characters, then you might need to apply a pattern search

WHERE Replace(ColumnName,' ','') like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

Cheers
MIK

Edited by - MIK_2008 on 03/11/2014 10:01:52
Go to Top of Page

ntn104
Posting Yak Master

175 Posts

Posted - 03/11/2014 :  11:26:09  Show Profile  Reply with Quote
Thanks, this method works.

quote:
Originally posted by MIK_2008

Replace function might help you in this case...

e.g. Len(Replace(ColumnName,' ',''))=9

And if there are non numeric characters others than spaces and your requirements are only to look for numeric with the length of 9 characters, then you might need to apply a pattern search

WHERE Replace(ColumnName,' ','') like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

Cheers
MIK

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.09 seconds. Powered By: Snitz Forums 2000