June 19, 2012 at 3:19 am
Hi All,
I am new to SQL, Can anyone help me with the Substring/ or other function to select only the number from the following list.
100034/com.ccs.ccscontact
1003/com.ccs.ccscontact
100/com.ccs.ccscontact
20005/com.ccs.ccscontact
Thank you in advance.
Mg
June 19, 2012 at 3:40 am
June 19, 2012 at 4:11 am
There's also a PATINDEX solution to this.
DECLARE @t TABLE (mystring VARCHAR(100))
INSERT INTO @t
SELECT '100034/com.ccs.ccscontact'
UNION ALL SELECT '1003/com.ccs.ccscontact'
UNION ALL SELECT '100/com.ccs.ccscontact'
UNION ALL SELECT '20005/com.ccs.ccscontact'
SELECT SUBSTRING(mystring, 1, PATINDEX('%[^0-9]%', mystring)-1)
FROM @t
Just guessing but the CHARINDEX approach is probably fastest (where's Cadavre when you need him :-P).
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 19, 2012 at 5:05 am
dwain.c (6/19/2012)
There's also a PATINDEX solution to this.
DECLARE @t TABLE (mystring VARCHAR(100))
INSERT INTO @t
SELECT '100034/com.ccs.ccscontact'
UNION ALL SELECT '1003/com.ccs.ccscontact'
UNION ALL SELECT '100/com.ccs.ccscontact'
UNION ALL SELECT '20005/com.ccs.ccscontact'
SELECT SUBSTRING(mystring, 1, PATINDEX('%[^0-9]%', mystring)-1)
FROM @t
Just guessing but the CHARINDEX approach is probably fastest (where's Cadavre when you need him :-P).
I beat you to the ParseName this time Dwain....:-P
Thats 1-1 for today between us....lol 😀
June 19, 2012 at 5:41 am
vinu512 (6/19/2012)
dwain.c (6/19/2012)
There's also a PATINDEX solution to this.
DECLARE @t TABLE (mystring VARCHAR(100))
INSERT INTO @t
SELECT '100034/com.ccs.ccscontact'
UNION ALL SELECT '1003/com.ccs.ccscontact'
UNION ALL SELECT '100/com.ccs.ccscontact'
UNION ALL SELECT '20005/com.ccs.ccscontact'
SELECT SUBSTRING(mystring, 1, PATINDEX('%[^0-9]%', mystring)-1)
FROM @t
Just guessing but the CHARINDEX approach is probably fastest (where's Cadavre when you need him :-P).
I beat you to the ParseName this time Dwain....:-P
Thats 1-1 for today between us....lol 😀
Sorry if I'm too blunt but after Cadavre's dissection, I'm not sure I'll be using PARSENAME again. 🙂
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 19, 2012 at 5:56 am
dwain.c (6/19/2012)
Just guessing but the CHARINDEX approach is probably fastest (where's Cadavre when you need him :-P).
Careful what you ask for!
SET NOCOUNT ON;
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment;
END;
SELECT TOP 1000000 IDENTITY(INT,1,1) AS ID,
CAST(nmbr AS VARCHAR(6)) + '/' + chr AS column1
INTO #testEnvironment
FROM (SELECT chr
FROM (VALUES('com.ccs.ccscontact'))a(chr)
) a
CROSS JOIN (SELECT (ABS(CHECKSUM(NEWID())) % 200000) + 1 AS nmbr
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3
)b;
CREATE CLUSTERED INDEX idx_clu_testEnvironment_ID ON #testEnvironment (ID);
DECLARE @HOLDER AS INT;
PRINT '========== BASELINE ==========';
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT @HOLDER = COUNT(*)
FROM #testEnvironment;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
PRINT REPLICATE('=',80);
PRINT '========== LEFT / CHARINDEX ==========';
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT @HOLDER = LEFT(column1, CHARINDEX('/', column1)-1)
FROM #testEnvironment;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
PRINT REPLICATE('=',80);
PRINT '========== PARSENAME / REPLACE ==========';
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT @HOLDER = PARSENAME(REPLACE(column1, '/', '.'), 4)
FROM #testEnvironment;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
PRINT REPLICATE('=',80);
PRINT '========== SUBSTRING / PATINDEX ==========';
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT @HOLDER = SUBSTRING(column1, 1, PATINDEX('%[^0-9]%', column1)-1)
FROM #testEnvironment;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
PRINT REPLICATE('=',80);
PRINT '========== SUBSTRING / CHARINDEX ==========';
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT @HOLDER = SUBSTRING(column1, 1, CHARINDEX('/', column1)-1)
FROM #testEnvironment;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
PRINT REPLICATE('=',80);
========== BASELINE ==========
Table '#testEnvironment'. Scan count 5, logical reads 5392, physical reads 0, read-ahead reads 8, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 110 ms, elapsed time = 39 ms.
================================================================================
========== LEFT / CHARINDEX ==========
Table '#testEnvironment'. Scan count 1, logical reads 5392, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 1313 ms, elapsed time = 1335 ms.
================================================================================
========== PARSENAME / REPLACE ==========
Table '#testEnvironment'. Scan count 1, logical reads 5392, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 3812 ms, elapsed time = 3825 ms.
================================================================================
========== SUBSTRING / PATINDEX ==========
Table '#testEnvironment'. Scan count 1, logical reads 5392, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 1844 ms, elapsed time = 1850 ms.
================================================================================
========== SUBSTRING / CHARINDEX ==========
Table '#testEnvironment'. Scan count 1, logical reads 5392, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 1281 ms, elapsed time = 1289 ms.
================================================================================
Second run: -
========== BASELINE ==========
Table '#testEnvironment'. Scan count 5, logical reads 5392, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 94 ms, elapsed time = 45 ms.
================================================================================
========== LEFT / CHARINDEX ==========
Table '#testEnvironment'. Scan count 1, logical reads 5392, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 1313 ms, elapsed time = 1318 ms.
================================================================================
========== PARSENAME / REPLACE ==========
Table '#testEnvironment'. Scan count 1, logical reads 5392, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 3953 ms, elapsed time = 3975 ms.
================================================================================
========== SUBSTRING / PATINDEX ==========
Table '#testEnvironment'. Scan count 1, logical reads 5392, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 1907 ms, elapsed time = 1912 ms.
================================================================================
========== SUBSTRING / CHARINDEX ==========
Table '#testEnvironment'. Scan count 1, logical reads 5392, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 1296 ms, elapsed time = 1305 ms.
================================================================================
dwain.c (6/19/2012)
There's also a PATINDEX solution to this.
So after two runs: -
Baseline: CPU 110ms, 94ms. Elapsed time 39ms, 45ms
LEFT / CHARINDEX: CPU 1313ms, 1313ms. Elapsed time 1335ms, 1318ms
PARSENAME / REPLACE: CPU 3812ms, 3953ms. Elapsed time 3825ms, 3975ms
SUBSTRING / PATINDEX: CPU 1844ms, 1907ms. Elapsed time 1850ms, 1912ms
SUBSTRING / CHARINDEX: CPU 1281ms, 1296ms. Elapsed time 1289ms, 1305ms
dwain.c (6/19/2012)
Sorry if I'm too blunt but after Cadavre's dissection, I'm not sure I'll be using PARSENAME again. 🙂
PATINDEX doesn't look good either 😉
June 19, 2012 at 6:05 am
My guess was right then.
BTW. I meant the dissection by Cadavre not dissection of Cadavre, in case anyone was wondering. 😀
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply