How would I retrieve data based on the length of string only?

Question

How would I retrieve data based on the length of string only?

davidsalazar01

SSCarpal Tunnel

Points: 4072
More actions
January 11, 2010 at 5:18 pm

#219134

Hello All,
I'm trying to retrieve only records that are ONLY 8 digits long and the datatype of the column is an int. Then once I retrieve those records I'd like to compare them to another set of records that I SUBSTRING(a.AcctNum, 3, 10) to check if there are going to be dups. What's the best way to do this?
Thanks,
David :-D:-D

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

davidsalazar01 (1/11/2010)
Hello All,
I'm trying to retrieve only records that are ONLY 8 digits long and the datatype of the column is an int. Then once I retrieve those records I'd like to compare them to another set of records that I SUBSTRING(a.AcctNum, 3, 10) to check if there are going to be dups. What's the best way to do this?
Thanks,
David :-D:-D

if the datatype is int, shouldn't you do math on hte datatype instead of trying to do substrings to it?

i would just do something like this:

WHERE a.AcctNum between 10000000 --the smallest 8 digit integer

AND 99999999 --the largest 8 digit integer

then you could group by a.AcctNum having count(*) > 1 to find dupes.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

davidsalazar01 SSCarpal Tunnel Points: 4072 More actions · Answer 2

What I'm trying to do is compare acct nums that are 8 digits long and a substring'ed 10 digit long acct num (i.e. SUBSTRING(AcctNum, 3, 8) because I'm planning on updating the 10 digit long acct nums and make them 8 digits by removing '00' in the front of the 10 digit acct num string. These 8 and 10 digit acct nums are in the same table.

Does that make sense? 😉

arun.sas SSChampion Points: 11831 More actions · Answer 3

Hi David,

In first post you say the data type is int, but the account no having the 00 in front of the first 10 digit, this is confusion, check the data type of the column.

Account column is in the char or varchar then use this straightly,

update ACCOUNT_TABLE

set ACCOUNT_NO = right(ACCOUNT_NO,8)

where len(ACCOUNT_NO) = 10

jforehand SSC Veteran Points: 237 More actions · Answer 4

Hi David,

If you're just trying to do a bit of research to see which account numbers may give you grief, how about trying a something like this:

select t1.AcctNum, t2.AcctNum

from Your_Account_Table t1

join Your_Account_Table t2 on (right(t1.AcctNum,8) = t2.AcctNum)

where LEN(t1.AcctNum) = 10 and

LEFT(t1.AcctNum, 2) = '00'

Cheers,

Jenny

Chris Souchik SSC-Addicted Points: 429 More actions · Answer 5

I think you could do something like:

WHERE int_acct_num/10000000=1 AND int_acct_num=CAST(str_acct_num AS INT)