I have the following table with the column Brand need to extract the correct string as in the output table.
Source Table
| Brand |
|---|
| (NORLANE) MADDERN 1200 (CONTEMPORARY 1 FACADE) |
| do not useASCENT 2600 ASPEN |
| do not useEVOLUTION 2300 ONTARIO |
| do not useMERCHISTON 3800 TRADITIONAL |
| AERO 1600 BLAKE |
| ALBANY 1700 ASPEN (GENESIS) |
| Albion 161 - Matisse - Discontinued |
| Alexander 159 - Coastal |
| Almeda 344 - Huntington |
| Mendelsohn 234 Swift - Drysdale |
| Andre Swift 235 - Condor - In progress |
| HARLECH 333 |
| Seymour 284 - Drysdale - Master |
| OLSEN DUPLEX 2100 COMTEMPORARY (LOT 1 & 2) |
Output required
| Brand |
|---|
| MADDERN 1200 |
| ASCENT 2600 |
| EVOLUTION 2300 |
| MERCHISTON 3800 |
| AERO 1600 |
| ALBANY 1700 |
| Albion 161 |
| Alexander 159 |
| Almeda 344 |
| Mendelsohn 234 |
| Andre Swift 235 |
| HARLECH 333 |
| Seymour 284 |
| OLSEN DUPLEX 2100 |
Script
------------
SELECT *
INTO #tmpTest
FROM
(
SELECT '(NORLANE) MADDERN 1200 (CONTEMPORARY 1 FACADE)' Brand
UNION ALL
SELECT '**do not use**ASCENT 2600 ASPEN'
UNION ALL
SELECT '**do not use**EVOLUTION 2300 ONTARIO'
UNION ALL
SELECT '**do not use**MERCHISTON 3800 TRADITIONAL'
UNION ALL
SELECT 'AERO 1600 BLAKE'
UNION ALL
SELECT 'ALBANY 1700 ASPEN (GENESIS)'
UNION ALL
SELECT 'Albion 161 - Matisse - Discontinued'
UNION ALL
SELECT 'Alexander 159 - Coastal'
UNION ALL
SELECT 'Almeda 344 - Huntington'
UNION ALL
SELECT 'Mendelsohn 234 Swift - Drysdale'
UNION ALL
SELECT 'Andre Swift 235 - Condor - In progress'
UNION ALL
SELECT 'HARLECH 333'
UNION ALL
SELECT 'Seymour 284 - Drysdale - Master'
UNION ALL
SELECT 'OLSEN DUPLEX 2100 COMTEMPORARY (LOT 1 & 2)'
)RUsed the following query, there is an issue with the query which is not returning the proper output.
SELECT Brand,LEFT(s2.s, p2.p) as CorrectBrand
FROM #tmpTest
CROSS APPLY(SELECT REPLACE(Brand, '**do not use**',''))s1(s) /* Source string with text removed */
CROSS APPLY(SELECT CHARINDEX(' ', s1.s))p1(FirstSpace) /* Find the pos of first space */
CROSS APPLY(SELECT CHARINDEX(')', s1.s))b(ClosePar) /* Find pos of first close parenthesis */
CROSS APPLY(SELECT IIF(ClosePar < FirstSpace and ClosePar > 0, STUFF(s1.s, 1, ClosePar + 1, ''), s1.s))s2(s) /* Remove content up to first closing parenthesisif relevant */
CROSS APPLY(SELECT CHARINDEX(' ', s2.s, FirstSpace + 1))p2(p); /* Pos of second space */
1条答案
按热度按时间a11xaf1n1#
Approaches like this almost always lead to discovery of new exceptions, the better way to solve this is to store the correct strings. (Perhaps that's what you hope to do?)
Anyway, here is a similar approach but which meets the current expected result:
fiddle