Number Table and Split STring


 
--===== Create number table on-the-fly
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n<101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)
 

,mycte as (
select strCol , Row_NUmber() Over(Order By n) rn

, CAST(substring(strCol, n, charindex('/', strCol + '/', n) - n) as MONEY)  splitVal

from test
cross apply Nums
Where n <= len(strCol) AND substring('/' + strCol, n, 1) = '/' )
Select * from mycte 

WHERE rn=4


Drop table test 


 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/08de9a11-ed07-4e8a-834b-1d96a15900c8/substring-use-to-extract-portion-of-field?forum=transactsql

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s