Stack Overflow Asked on January 5, 2021
All,
I have a query like this:
SELECT
[RecName],
CHARINDEX('_',[Rec Name]),
CASE
WHEN CHARINDEX('_',[Rec Name]) >=1 THEN
LEFT([Rec Name],CHARINDEX('_',[Rec Name])-1)
ELSE
'NA'
END RecTrimmed
FROM calculated_data
WHERE [Rec Name] = 'T101184_7AB_C1_ABCDE'
GROUP BY [Rec Name]
When I execute the above I get the following result – T101184
How can I achieve the same result using Substring
?
Any lead would be appreciated.
Thanks,
John
One simple and quick solution could be to use an ordinal splitter to divide the string at '_' into rows. Then there's no need to fiddle around with LEFT and SUBSTRING and CHARINDEX.
The ordinal splitter can be found here
CREATE FUNCTION dbo.DelimitedSplit8K
--===== Define I/O parameters
(@pString VARCHAR(8000), @pDelimiter CHAR(1))
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE! IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
-- enough to cover VARCHAR(8000)
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
SELECT 1 UNION ALL
SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
SELECT s.N1,
ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
FROM cteStart s
)
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
Item = SUBSTRING(@pString, l.N1, l.L1)
FROM cteLen l
;
Then the query is very simple and executes efficiently
with cte (col) as (
select 'T101184_7AB_C1_ABCDE' union all
select 'T1011847ABC1ABCDE')
select Item
from cte c
cross apply
(select case when charindex('_',c.col)=0 then 'NA' else c.col end chr) ndx
cross apply
dbo.DelimitedSplit8K(ndx.chr, '_')
where ItemNumber=1;
Output
Item
T101184
NA
Answered by SteveC on January 5, 2021
One way is
with cte (col) as
(select 'T101184_7AB_C1_ABCDE' union all
select 'T1011847ABC1ABCDE')
select coalesce(nullif(substring(col,0,charindex('_',col)),''),'NA')
from cte;
Having said that, there is no reason you shouldn't use left
for the problem you have at hand
Answered by Rajat on January 5, 2021
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP