May 29, 2009 at 12:57 am
Hey guys, been struggling with this one for a bit and about to head home, but does anyone know how I'd get "18669911.96" out of this Text field?
"FFX Buy USD/Sell AUD, Broker 703 MATURITY 11/05/09 SOLD AUD 18,669,911.96"
It will always be the last value in the Field but couldn't use Right() not knowing if a trade would be $1 or $1,000,000,000, can't use CHARINDEX() - having excluded the first 20 or 30 characters to avoid the first mention of the Currency Codes - because there are plenty of options and the other numbers mean I can't just grab the numbers out of the Text field.
I see the solution as being to take everything to the right of the right-most space (having put it through RTRIM just to be safe), but can't find any examples of this to use.
Thanks,
Nathan
May 29, 2009 at 2:33 am
Pls try the following Query. It is Coming Correctly. I m not 100% Sure.. i m not checked for all cases.
Select reverse(Substring(Reverse('FFX Buy USD/Sell AUD, Broker 703 MATURITY 11/05/09 SOLD AUD 18,669,911.96'),
0,
Patindex('% %', reverse('FFX Buy USD/Sell AUD, Broker 703 MATURITY 11/05/09 SOLD AUD 18,669,911.96'))))
May 29, 2009 at 3:01 am
See if this helps.
; WITH TestCTE
AS
(
SELECT'FFX Buy USD/Sell AUD, Broker 703 MATURITY 11/05/09 SOLD AUD 18,669,911.96' AS SomeText
UNION ALL
SELECT'FFX Buy $/Sell $, Broker 471 MATURITY 11/05/09 SOLD $878.96' AS SomeText
UNION ALL
SELECT'FFX Buy $/Sell $, Broker 471 MATURITY 11/05/09 SOLD $48' AS SomeText
)
SELECTSomeText, RIGHT( RTRIM( SomeText ), CHARINDEX( ' ', REVERSE( RTRIM( SomeText ) ) + ' ' ) - 1 )
FROMTestCTE
--Ramesh
May 29, 2009 at 3:32 am
siva_pdm40 (5/29/2009)
This will work 100%....Kindly Reply me weather it's working or not...
Hi Siva,
your statement runs fine in only with round number,
try this
declare @abc varchar(50)
set @abc = 'THE GOOD DAY IS TODAY 100,00.00'
select @abc = replace(@ABC,' ','')
select @abc = rtrim(@ABC)
select right(@ABC,PATINDEX(('%[A-Z]%'),reverse(@ABC))-1)
RESULT
100,00.00
--Yours Select reverse(Substring(Reverse(@ABC),0,Patindex('%[^0-9]%', reverse(@ABC))))
RESULT
00
ARUN SAS
May 29, 2009 at 7:29 pm
Ramesh (5/29/2009)
See if this helps.
; WITH TestCTE
AS
(
SELECT'FFX Buy USD/Sell AUD, Broker 703 MATURITY 11/05/09 SOLD AUD 18,669,911.96' AS SomeText
UNION ALL
SELECT'FFX Buy $/Sell $, Broker 471 MATURITY 11/05/09 SOLD $878.96' AS SomeText
UNION ALL
SELECT'FFX Buy $/Sell $, Broker 471 MATURITY 11/05/09 SOLD $48' AS SomeText
)
SELECTSomeText, RIGHT( RTRIM( SomeText ), CHARINDEX( ' ', REVERSE( RTRIM( SomeText ) ) + ' ' ) - 1 )
FROMTestCTE
Spot on, Ramesh. And, you made it easy to convert to the MONEY datatype so you can use the result number for calculations.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 29, 2009 at 9:13 pm
Spot on, Ramesh. And, you made it easy to convert to the MONEY datatype so you can use the result number for calculations.
Hi jeff,
Ok with the Ramesh statements,
But, for the text like “THE GOOD DAY IS TODAY$100,00.00'”
(i.e. without the space)
Remesh Result
--TODAY$100,00.00
But using this
declare @abc varchar(50)
set @abc = 'THE GOOD DAY IS TODAY$100,00.00'
select right(@ABC,PATINDEX(('%[A-Z]%'),reverse(@ABC))-1)
give us the correct
Result
--$100,00.00
Which one is better in all Situations?
ARUN SAS
May 30, 2009 at 1:45 am
Jeff Moden (5/29/2009)
Spot on, Ramesh. And, you made it easy to convert to the MONEY datatype so you can use the result number for calculations.
Thanks Jeff:-), it just that one of the things that I learned from SSC.
--Ramesh
May 30, 2009 at 1:52 am
arun.sas (5/29/2009)
Which one is better in all Situations?ARUN SAS
If you read the OP's original post he has mentioned that he wanted the entire text that follows the last space.
Though your solution can be modified to meet the OP's requirements but it probably would be slower on larger resultsets because of the pattern search. Am I correct, Jeff?
--Ramesh
May 30, 2009 at 2:00 am
arun.sas (5/29/2009)
Which one is better in all Situations?
ARUN SAS
If you read the OP's original post he has mentioned that he wanted the entire text that follows the last space.
Though your solution can be modified to meet the OP's requirements but it probably would be slower on larger resultsets because of the pattern search. Am I correct, Jeff?
--Ramesh
May 30, 2009 at 1:01 pm
arun.sas (5/29/2009)
Spot on, Ramesh. And, you made it easy to convert to the MONEY datatype so you can use the result number for calculations.
Hi jeff,
Ok with the Ramesh statements,
But, for the text like “THE GOOD DAY IS TODAY$100,00.00'”
(i.e. without the space)
Remesh Result
--TODAY$100,00.00
But using this
declare @abc varchar(50)
set @abc = 'THE GOOD DAY IS TODAY$100,00.00'
select right(@ABC,PATINDEX(('%[A-Z]%'),reverse(@ABC))-1)
give us the correct
Result
--$100,00.00
Which one is better in [font="Arial Black"]all[/font] Situations?
ARUN SAS
Heh... no food fights, folks. 😛
Actually... neither "is better in all situations". Ramesh's very nicely solves the original problem of finding the operand to the right of the last embedded space in the string. Your's very nicely finds the operand to the right of the last embedded letter in the string and leaves a leading space if one is present (which, of course, is easily fixed).
What if you have something like any of the following?
DECLARE @abc VARCHAR(50)
SELECT @abc = 'Today is day 1#$100,00.00 '
SELECT RIGHT(@ABC,PATINDEX(('%[A-Z]%'),REVERSE(@ABC))-1)
SELECT RIGHT( RTRIM( @abc ), CHARINDEX( ' ', REVERSE( RTRIM( @abc ) ) + ' ' ) - 1 )
Both methods will fail if the problem definition is to get the right most numeric value (if there is one) along with a "$" if present because both will return too much... both will return the "1#".
The following method isn't perfect for "all situations" either, but it handles more than either of the two methods above.
DECLARE @abc VARCHAR(50)
SELECT @abc = 'Today is day 1#$100,00.00 '
SELECT RIGHT(d.ABC,PATINDEX(('%[^-+0-9,.$]%'),REVERSE(d.ABC))-1)
FROM (SELECT RTRIM(@ABC) AS ABC) d
But, even that won't withstand something like the following...
DECLARE @abc VARCHAR(50)
SELECT @abc = 'Today is day 1$100,00.00 '
SELECT RIGHT(d.ABC,PATINDEX(('%[^-+0-9,.$]%'),REVERSE(d.ABC))-1)
FROM (SELECT RTRIM(@ABC) AS ABC) d
Of course, the example data line the OP gave had no "$" sign in it, so technically, the following would be correct...
DECLARE @abc VARCHAR(50)
SELECT @abc = 'Today is day 1$100,00.00 '
SELECT RIGHT(d.ABC,PATINDEX(('%[^-+0-9,.]%'),REVERSE(d.ABC))-1)
FROM (SELECT RTRIM(@ABC) AS ABC) d
So, what is best to meet "all situations"? None of the above because, in theory, you could also have the following... 😛
DECLARE @abc VARCHAR(50)
SELECT @abc = 'Today is day 1$,,,100,00.00 '
SELECT RIGHT(d.ABC,PATINDEX(('%[^-+0-9,.]%'),REVERSE(d.ABC))-1)
FROM (SELECT RTRIM(@ABC) AS ABC) d
Ramesh's code did meet the requirements for the precise stated problem, though.
Personally, I'd hunt down and feed some very high velocity pork chops to the person who provided data in such an ill conceived manner to begin with. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
May 30, 2009 at 1:34 pm
Ramesh (5/30/2009)
arun.sas (5/29/2009)
Which one is better in all Situations?
ARUN SAS
If you read the OP's original post he has mentioned that he wanted the entire text that follows the last space.
Though your solution can be modified to meet the OP's requirements but it probably would be slower on larger resultsets because of the pattern search. Am I correct, Jeff?
Good question... I've never actually tested it. My inclination would be to say that CHARINDEX will be slightly faster than PATINDEX. But you know I don't speculate. Let's test and find out because "A Developer must not guess... a Developer must KNOW." 😉
Here's a million row test table that creates a 32 character column of letters and digits all mixed up...
DROP TABLE JBMTest
GO
--===== Create and populate a 1,000,000 row test table.
SELECT TOP 1000000
SomeID = IDENTITY(INT,1,1),
SomeHex = CAST(REPLACE(NEWID(),'-','') AS CHAR(32))
INTO dbo.JBMTest
FROM Master.dbo.SysColumns t1,
Master.dbo.SysColumns t2 --Lack of join criteria makes this a CROSS-JOIN
--===== A table is not properly formed unless a clustered index has been assigned
-- Takes about 1 second to execute.
ALTER TABLE dbo.JBMTest
ADD PRIMARY KEY CLUSTERED (SomeID)
And here's a comparison between CHARINDEX and PATINDEX. Notice how the results are thrown away using the @Bitbucket variable to keep the display out of the picture...
--===== Supress the auto-display of rowcounts
SET NOCOUNT ON
--===== Create a place to throw things away
DECLARE @Bitbucket INT
--===== Turn on the timers and test the two functions
-- throwing away the results to keep the measurement
-- as pure as possible.
SET STATISTICS TIME ON
--===== Test for two given characters (CHARINDEX)
SELECT @Bitbucket = CHARINDEX('0d',SomeHex)
FROM dbo.JBMTest
--===== Test for two given characters (PATINDEX)
SELECT @Bitbucket = PATINDEX('%0d%',SomeHex)
FROM dbo.JBMTest
--===== Test for one given character (CHARINDEX)
SELECT @Bitbucket = CHARINDEX('d',SomeHex)
FROM dbo.JBMTest
--===== Test for one given character (PATINDEX)
SELECT @Bitbucket = PATINDEX('%d%',SomeHex)
FROM dbo.JBMTest
--===== Test for one given character as a range (PATINDEX)
SELECT @Bitbucket = PATINDEX('%[d-d]%',SomeHex)
FROM dbo.JBMTest
SET STATISTICS TIME OFF
That yields the following on by humble desktop box...
[font="Courier New"]
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 1813 ms, elapsed time = 1826 ms.
SQL Server Execution Times:
CPU time = 2234 ms, elapsed time = 2239 ms.
SQL Server Execution Times:
CPU time = 1312 ms, elapsed time = 1312 ms.
SQL Server Execution Times:
CPU time = 1416 ms, elapsed time = 1416 ms.
SQL Server Execution Times:
CPU time = 2312 ms, elapsed time = 2312 ms.
[/font]
Now I can say that CHARINDEX is faster than PATINDEX especially if you're looking for just one character. But, over a million rows, it's still a pretty close race and PATINDEX may bring in some additional functionality like it did in some of the examples I presented in my previous post for this problem.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 30, 2009 at 6:44 pm
NathanB (5/29/2009)
Hey guys, been struggling with this one for a bit and about to head home, but does anyone know how I'd get "18669911.96" out of this Text field?"FFX Buy USD/Sell AUD, Broker 703 MATURITY 11/05/09 SOLD AUD 18,669,911.96"
It will always be the last value in the Field but couldn't use Right() ...
using a delimiting table valued function the solution is straightforward. My fListToVarchars() (link to related topic with source) would return a table of ( item, itemSequence ).
select ST.fieldOfInterest, reverse(X.item) as lastItem
from SomeTable as ST
cross apply global.dbo.fListToVarchars( reverse( ST.fieldOfInterest ), ' ' ) as X
where X.itemSequence = 1
May 31, 2009 at 2:46 am
Hey Jeff, I really appreciate you for taking time in detailing the solutions, listing out the issues with them & testing them.
BTW, I am ducking down:hehe:, in case the high velocity pork chops are hitting in my way:-D
--Ramesh
May 31, 2009 at 10:23 am
antonio.collins (5/30/2009)
NathanB (5/29/2009)
Hey guys, been struggling with this one for a bit and about to head home, but does anyone know how I'd get "18669911.96" out of this Text field?"FFX Buy USD/Sell AUD, Broker 703 MATURITY 11/05/09 SOLD AUD 18,669,911.96"
It will always be the last value in the Field but couldn't use Right() ...
using a delimiting table valued function the solution is straightforward. My fListToVarchars() (link to related topic with source) would return a table of ( item, itemSequence ).
select ST.fieldOfInterest, reverse(X.item) as lastItemfrom SomeTable as ST
cross apply global.dbo.fListToVarchars( reverse( ST.fieldOfInterest ), ' ' ) as X
where X.itemSequence = 1
There's a very steep penalty in performance in all of that, though. XML splitters are quite a bit slower than Tally table splitters and the overhead of calling a function on something like this is a real killer. Of course... here's the test that shows that...
First, the test code with to generate just 10,000 rows of data...
--===== Create and populate a test table.
SELECT TOP 10000 --<<<==== Change this number to vary the rows in the test
SomeID = IDENTITY(INT,1,1),
SomeString = CAST(REPLACE(NEWID(),'-',' ') AS CHAR(32)) + ' '
+ CONVERT(VARCHAR(20),CAST(CHECKSUM(NEWID())/100.0 AS MONEY),1)
INTO dbo.JBMTest
FROM Master.dbo.SysColumns t1
CROSS JOIN
Master.dbo.SysColumns t2
--===== A table is not properly formed unless a clustered index has been assigned
-- Takes about 1 second to execute.
ALTER TABLE dbo.JBMTest
ADD PRIMARY KEY CLUSTERED (SomeID)
Here's the performance test between your function and Ramesh's...
--===== Supress the auto-display of rowcounts
SET NOCOUNT ON
--===== Create a place to throw things away
DECLARE @Bitbucket VARCHAR(50)
--
--===== Antonio's Function =====
PRINT REPLICATE('=',78)
PRINT '--===== Antonio''s Function ====='
SET STATISTICS TIME ON
select @Bitbucket = Reverse(X.item)
from dbo.JBMTest as ST
cross apply dbo.fListToVarchars( reverse( ST.SomeString ), ' ' ) as X
where X.itemSequence = 1
SET STATISTICS TIME OFF
--
--===== Ramesh''s Inline Code =====
PRINT REPLICATE('=',78)
PRINT '--===== Ramesh''s Inline Code ====='
SET STATISTICS TIME ON
SELECT @Bitbucket = RIGHT( RTRIM( SomeString ), CHARINDEX( ' ', REVERSE( RTRIM( SomeString ) ) + ' ' ) - 1 )
FROM dbo.JBMTest
SET STATISTICS TIME OFF
And, here's the results...
[font="Courier New"]==============================================================================
--===== Antonio's Function =====
SQL Server Execution Times:
CPU time = 22156 ms, elapsed time = 25535 ms.
==============================================================================
--===== Ramesh's Inline Code =====
SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 36 ms.
[/font]
Keep in mind that was 22 seconds to process just 10,000 rows. Generic functions that can solve "ALL" problems normally become a problem themselves. Unless they have proven performance characteristics, I generally steer away from such functions in favor of inline functionality. And, if you take a look, the inline functionality has less complicated code in this instance, as well.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply