How would you parse 000.024.000 into 24H?

Question

How would you parse 000.024.000 into 24H?

NineIron

SSChampion

Points: 12536
More actions
March 28, 2018 at 6:22 am

#330069

My table holds values like 000.004.000, 000.02.000, 000.024.000. What is the best/easiest way to get 4H, 2H, or 24H respectively?

Viewing 15 posts - 1 through 15 (of 17 total)

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

pietlinden SSC Guru Points: 63503 More actions · Answer 1

Got lazy and used DelimitedSplit8K

SELECT TextTime , ca.Item , ca.ItemNumber FROM (SELECT '000.004.000' AS TextTime UNION ALL SELECT '000.02.000' UNION ALL SELECT '000.024.000') x CROSS APPLY Scratchpad.dbo.DelimitedSplit8K(x.TextTime,'.') ca WHERE ca.ItemNumber = 2;

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 2

Another method, slightly more efficient
😎
WITH SAMPLE_DATA(TSTR) AS ( SELECT '000.004.000' UNION ALL SELECT '000.02.000' UNION ALL SELECT '000.024.000' ) SELECT SD.TSTR ,CONVERT(INT,REPLACE(REPLACE(SD.TSTR,'000.',''),'.000',''),0) AS NUM_HOURS FROM SAMPLE_DATA SD;

Output
TSTR NUM_HOURS ----------- ----------- 000.004.000 4 000.02.000 2 000.024.000 24

NineIron SSChampion Points: 12536 More actions · Answer 3

Thanx but, I was hoping for something I can use as a field value.

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 4

NineIron - Wednesday, March 28, 2018 7:01 AM
Thanx but, I was hoping for something I can use as a field value.

Now you have to explain exactly what you want!
😎

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 5

NineIron - Wednesday, March 28, 2018 6:22 AM
My table holds values like 000.004.000, 000.02.000, 000.024.000. What is the best/easiest way to get 4H, 2H, or 24H respectively?

RIGHT 2 of LEFT(CHARINDEX second decimal.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

pietlinden SSC Guru Points: 63503 More actions · Answer 6

NineIron - Wednesday, March 28, 2018 7:01 AM
Thanx but, I was hoping for something I can use as a field value.

You should have said that in the first place. "Use as a field value" how? How about an example? "I have X, I need Y, so I can do Z with it"?

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 7

The OP will have to explain why 024 = 24 and 02 = 2 and not 20
😎

NineIron SSChampion Points: 12536 More actions · Answer 8

NineIron

SSChampion

Points: 12536

March 28, 2018 at 7:21 am

#1984782

Thanx.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 9

NineIron - Wednesday, March 28, 2018 6:22 AM
My table holds values like 000.004.000, 000.02.000, 000.024.000. What is the best/easiest way to get 4H, 2H, or 24H respectively?

If the data always has 3 parts separated by periods as you've indicated above, then this is simple...

SELECT PARSENAME(TextTime,2)+'H'
FROM dbo.YourTable

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

NineIron SSChampion Points: 12536 More actions · Answer 10

Nice. I'll use replace(PARSENAME(TextTime,2)+'H','0','') to remove the leading zero's but, this is perfect.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 11

NineIron - Wednesday, March 28, 2018 7:41 AM
Nice. I'll use replace(PARSENAME(TextTime,2)+'H','0','') to remove the leading zero's but, this is perfect.

No... do no use REPLACE here because it will change things like 020 to just 2. Convert to an INT and then back to character based to add the "H".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

NineIron SSChampion Points: 12536 More actions · Answer 12

NineIron

SSChampion

Points: 12536

March 28, 2018 at 7:46 am

#1984797

k.

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 13

Jeff Moden - Wednesday, March 28, 2018 7:44 AM
NineIron - Wednesday, March 28, 2018 7:41 AM
Nice. I'll use replace(PARSENAME(TextTime,2)+'H','0','') to remove the leading zero's but, this is perfect.
No... do no use REPLACE here because it will change things like 020 to just 2. Convert to an INT and then back to character based to add the "H".

Or use the CONCAT function...
DECLARE @TextTime VARCHAR(11) = '000.002.000';

SELECT CONCAT(CONVERT(INT, PARSENAME(@TextTime, 2)), 'H');

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 14

Jason A. Long - Wednesday, March 28, 2018 10:58 AM
Jeff Moden - Wednesday, March 28, 2018 7:44 AM
NineIron - Wednesday, March 28, 2018 7:41 AM
Nice. I'll use replace(PARSENAME(TextTime,2)+'H','0','') to remove the leading zero's but, this is perfect.
No... do no use REPLACE here because it will change things like 020 to just 2. Convert to an INT and then back to character based to add the "H".
Or use the CONCAT function...
DECLARE @TextTime VARCHAR(11) = '000.002.000';
SELECT CONCAT(CONVERT(INT, PARSENAME(@TextTime, 2)), 'H');

Damn. I hate what MS does behind the scenes on these supposed improvements. Here's what PARSNAME and CONCAT renders out to. I'd forgotten that PARSNAME produces an NVARCHAR result.

[Expr1093] = Scalar Operator(concat(CONVERT_IMPLICIT(varchar(12),CONVERT(int,parsename(CONVERT_IMPLICIT(nvarchar(11),[@TextTime],0),(2)),0),0),'H'))

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)