SQL Server SQL Query to pick specific string from the table [closed]

tvokkenx 于 2023-06-28 发布在其他

关注(0)|答案(2)|浏览(114)

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.

sql-server

来源：https://stackoverflow.com/questions/76570163/sql-query-to-pick-specific-string-from-the-table

2条答案

按热度按时间

c9qzyr3d1#

On the assumption your output requirement is to

Remove the "do not use" text
Ignore the first word in parenthesis if it exists
Take the next two text elements delimited by a space

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:

select Left(s2.s, p2.p) as Brand
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 */

Result:

赞(0）回复(0）举报 2023-06-28

2izufjch2#

As an example :

SELECT RIGHT(Brand, LEN(Brand) - CASE PATINDEX('**do not use**%', Brand) WHEN 1 THEN 15 ELSE 0 END)
FROM   #tmpTest

赞(0）回复(0）举报 2023-06-28

我来回答

SQL Server SQL Query to pick specific string from the table [closed]

2条答案

相关问题

热门标签

最新问答