Problem with Right substring - SQL Server Forums

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

Problem with Right substring

Author

Topic

DaveBF
Yak Posting Veteran

89 Posts

Posted - 2010-06-23 : 08:18:31

I have a table of Social Security numbers. I'm trying to pick off the last 4 digits using: RIGHT(SSN,(4)). The number of digits I actually get seems to depend on whether the first digit is a zero.

For example here is a select statement followed by some of my returned data.

SELECT SSN,RIGHT(SSN,(4)),LEN(SSN) FROM Person

099620737 737 9
099661294 294 9
100286238 6238 9
100688090 8090 9

Could someone explain what's going on?
Thanks.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-23 : 08:23:12

What do you see here?

SELECT SSN,RIGHT(SSN,(4)),LEN(SSN) from
(
select 099620737 as ssn union all
select 099661294 union all
select 100286238 union all
select 100688090
) as t

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-23 : 08:24:00

there are blanks involved.
Use LTRIM() and / or RTRIM()

No, you're never too old to Yak'n'Roll if you're too young to die.

DaveBF
Yak Posting Veteran

89 Posts

Posted - 2010-06-23 : 08:25:19

I see this:
99620737 0737 8
99661294 1294 8
100286238 6238 9
100688090 8090 9

But sorry, I don't know what you're telling me...

DaveBF
Yak Posting Veteran

89 Posts

Posted - 2010-06-23 : 08:27:15

[quote]Originally posted by webfred

there are blanks involved.
Use LTRIM() and / or RTRIM()

Got it. Thank you.

SELECT SSN,RIGHT(RTRIM(SSN),(4)),LEN(SSN)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-23 : 08:30:43

quote:
Originally posted by DaveBF

[quote]Originally posted by webfred

there are blanks involved.
Use LTRIM() and / or RTRIM()

Got it. Thank you.

SELECT SSN,RIGHT(RTRIM(SSN),(4)),LEN(SSN)

Then the column has CHAR datatpye which has finxed length

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-23 : 08:34:35

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources