April 8, 2009 at 12:01 pm
I have the following data with a data type of varchar(14)
05380101010000
05380101020000
05380101101000
05380011103000
I need to to get rid of the leading zeros after I substring the numbers I want out.
Results:
101
102
1101
1103
April 8, 2009 at 12:04 pm
Can you cast/convert them into a numeric data type (int, for example)? That'll get rid of leading zeroes.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 8, 2009 at 12:05 pm
Leading zeros or trailling zeros? looking at the data, I'm thinking trailing.
April 8, 2009 at 12:08 pm
In this case I have both.
April 8, 2009 at 12:14 pm
Show how you are pulling out the values from the initial values.
April 8, 2009 at 12:20 pm
I am pulling them like this:
SUBSTRING(REPLACE(LTRIM(REPLACE(col1, '0', ' ')), ' ', '0'), 7, 3)
This works for the the first two numbers but not for the last two.
Outputs the following:
101
102
101 but needs to be 1101
April 8, 2009 at 12:28 pm
I'm confused. Hightlight (BOLD) the values you are trying to extract:
05380101010000
05380101020000
05380101101000
05380011103000
April 8, 2009 at 12:30 pm
05380101010000
05380101020000
05380101101000
05380011103000
April 8, 2009 at 12:33 pm
I don't see where leading zeroes have anything to do with that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 8, 2009 at 12:36 pm
Try this:
select reverse(cast(cast(reverse(substring(col1,8,4)) as int) as varchar(4)))
April 8, 2009 at 12:36 pm
This should get you what you need. Just put in your column name where I have the string literal.
select reverse(cast(cast(reverse(substring('05380101010000', 8, 4)) as int) as varchar(4)))
Edit: And, of course, we both post the solution at the same time. 🙂
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 8, 2009 at 12:42 pm
Not only that, but that is what I thought was needed from the start, but I got confused by the subsequent posts until I had the OP show what was needed.
April 8, 2009 at 12:52 pm
Yeah, I took it at face value. I still don't understand what this has to do with leading zeroes, like the title.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 8, 2009 at 1:02 pm
Seems an odd requirement, but this is an alternative:
SELECT SUBSTRING(Field1, 8, 8 - PATINDEX('%[1-9]%', REVERSE(Field1)))
FROM (
SELECT '05380101010000' AS Field1 UNION ALL
SELECT '05380101020000' UNION ALL
SELECT '05380101101000' UNION ALL
SELECT '05380011103000'
) TestData
April 8, 2009 at 1:12 pm
Sorry about the title...but this is the correct way to get what I wanted. Your way still left off the end numbers.
SUBSTRING(col,PATINDEX('%[^0]%',col),10)
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply