Splitting data based on format in SQL| LEAD() | SubString | Replace | CharIndex | recursive CTE|V24
CREATE TABLE Emails (ID INT, Name1 varchar(500))
INSERT Emails VALUES (1, 'www.server1.com : OK www.server2.in : OK www.server3.net : Not OK www.server4.com : OK' )
SELECT * FROM Emails
drop table IF EXISTS #test
;with T as (
select ID, 1 as row, charindex('www.', Name1) pos, Name1 from Emails
union all
select ID, pos + 1, charindex('www.', Name1, pos + 1), Name1
from T
where pos (greaterthan symbol) 0
)
select --* from T
ID, Name1, pos
,LEAD(pos,1) OVER(PARTITION BY ID ORDER BY pos) as pos2 INTO #test from T
where pos (greaterthan symbol) 0
order by Name1, pos
select * from #test order by pos2
SELECT ID, REPLACE(REPLACE(Name1,': Not OK',''),': OK','') Name_Derived
, CASE WHEN Name1 LIKE '%Not OK%' THEN 'Not OK' ELSE 'OK' END Status_Derived FROM
(
select ID, CASE WHEN pos (lessthansymbol) pos2 THEN SUBSTRING(Name1, pos,(pos2-pos))
ELSE SUBSTRING(Name1,pos,(LEN(Name1)-pos)+1) END Name1 from #test
) DT
INSERT Emails VALUES (2, 'www.server5.com : OK www.server6.in : OK www.server7.net : Not OK www.server8.com : OK' )
INSERT Emails VALUES (3, 'www.serwer9.com : OK www.serwer10.in : OK www.server11.net : Not OK www.serWer12.com : OK' )
INSERT Emails VALUES (4, 'www.s13.org : OK www.se14.com : Not OK www.ser15.sg : OK www.serw16.com : OK' )
INSERT Emails VALUES (5, 'www.server17.net : Not OK www.server18.com : OK')
Video 23: Missing Sequence Number in SQL | Recursive CTE | Generate Sequence Number --* • Missing Sequence Number in SQL | Recursiv...
Video 22: Comma Separated values to multiple rows | string_split() , Cross apply --** • Comma Separated values to multiple rows | ...
Video 21: XML Path in SQL - • Comma Separated values in SQL | XML PATH |...
Video 20: Count and While loop in SQL - • Count Function in SQL | Count_BIG() | Coun...
Video 19: Warnings SQL? | Null value is eliminated by an aggregate or other SET operation --* • Warning in SQL | Null value is eliminated ...
Video 18: Can we Update a View in SQL? --* • Can we Update a View in SQL? | SQL Intervi...
Video 17: Sorting in SQL | Sorting Interview Questions --* • Sorting in SQL | ORDER BY | Sorting Interv...
Text Version of Video: https://sqlwithravimartha.blogspot.co...
Video 16: Union Vs Union All | With Examples | Prerequisites for Union and Union All | Performance Comparison --* • Union Vs Union All | With Examples | Prere...
Video 15: Import data from File to table in SQL Server |.txt, .csv files to table|Import Flat File|Import Data --* • Import data from File to table in SQL Serv...
Video 14: Find Employees With Salary Lower Than Their Department Average in SQL --* • Find Employees Whose Salary Higher Than Th...
Video 13: Find Employees With Salary Lower Than Their Department Average in SQL --* • Find Employees With Salary Lower Than Thei...
Video 12: Import Tables from one Server to another Using SSMS | Import tables using SQL Query in SSMS --* • Import Tables from one Server to another U...
Video 11: Table Variables in SQL Server | Pound Tables | Table Variable in SQL | Table Variable in Tempdb --* • Table Variables in SQL Server | Pound Tabl...
Video 10: Quick Practical onTemporary tables in SQL Server | Temp Tables in SQL | Local Temp Table | Global Temp Table--* • Quick Practical on Temporary tables in SQL...
Video 9: Temporary tables in SQL Server | Temp Tables in SQL | Local Temp Table | Global Temp Table -- * • Temporary tables in SQL Server | Temp Tabl...
Video 8: Running total & Avg in SQL | Cumulative Sum & Avg in SQL | Calculating running total & Avg in SQL -- * • Running total & Avg in SQL | Cumulative Su...
Video 7 Title: Length Vs DataLength Vs ColumnLength | Len() | DataLength() | Col_Length | Column length in SQL -- * • Length Vs DataLength Vs ColumnLength | Len...
Video 6 Title: Tricky CASE Statement Interview Questions | CASE WHEN | WHEN Clause | CASE Statement in SQL -- * • Tricky CASE Statement Interview Questions ...
Video 5 Title: Delete Duplicates in SQL by retaining one Unique record | Row Duplicates | Business Key Duplicates -- * • Delete Duplicates in SQL by retaining one ...
Video 4 Title: Difference between TRUNCATE, DELETE and DROP -- * • Difference between TRUNCATE, DELETE and DR...
Video 3 Title: Sorting in SQL | Order By | Conditional Sorting| Unusual Sorting --* • Sorting in SQL | Order By | Conditional So...
Video 2 Title: Difference between Primary Key and Unique Key --* • Difference between Primary Key and Unique ...
Video 1 Title: Logical Processing of SELECT Statement -- * • Logical Processing of SELECT Statement | L...
SQL Step by Step Video 8 Title: UPDATE | DELETE | WHERE | ALTER | DROP in SQL -- * • UPDATE | DELETE | WHERE | ALTER | DROP in ...
Blog: https://sqlwithravimartha.blogspot.com/
Facebook Page:
/ sql-with-ravimartha-109497027391239
/ etl-dwh-testing-100494175057349
In questa pagina del sito puoi guardare il video online Splitting data based on format in SQL| LEAD() | SubString | Replace | CharIndex | recursive CTE|V24 della durata di ore minuti seconda in buona qualità , che l'utente ha caricato SQL With RaviMartha 23 gennaio 2021, condividi il link con amici e conoscenti, su youtube questo video è già stato visto 746 volte e gli è piaciuto 18 spettatori. Buona visione!
