Closed. This question needs details or clarity . It is not currently accepting answers.
Want to improve this question? Add details and clarify the problem by editing this post .
Closed 52 mins ago.
Improve this question
I have table with the following data:
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 |
Need to pick the specific string as output
Brand |
---|
MADDERN 1200 |
ASCENT 2600 |
EVOLUTION 2300 |
MERCHISTON 3800 |
AERO 1600 |
ALBANY 1700 |
Albion 161 |
Alexander 159 |
Almeda 344 |
Mendelsohn 234 |
Following is the script of the table:
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'
)R
SELECT * FROM #tmpTest
I have tired with CHARINDEX of with space
CHARINDEX(' ',REVERSE(@str)) ,RIGHT(@str,CHARINDEX(' ',REVERSE(@str))-1)
But not returning proper output.
2条答案
按热度按时间c9qzyr3d1#
On the assumption your output requirement is to
You can apply each successive step in a series of cross apply that each builds on the previous result.
Note this is possibly not the final working solution you'll need since it's based only on the sample data you've supplied, but it a good way there:
Result:
2izufjch2#
As an example :