CODEWITHSUNDEEP
sql-server
mrbungle
mrbungle

Reputation: 1931

Find substring and then extract preceding substring

Been banging my head around this this morning. Say I have a strings

The key word here is "needed". I can find that and the position but I'm having problem pulling the number preceding it which can vary in lenght. I've tried variations of this and just not getting it.

Select Left(SubString(Notes, PatIndex('%needed%', Notes), 8000), PatIndex('%needed%', SubString(Notes, PatIndex('%needed%', Notes), 8000))+1)FROM ....WHERE ....

Can someone point me in the right direction or perhaps assist with the code? Thanks in advance.

Upvotes: 0

Views: 65

Answers (4)

mrbungle
mrbungle

Reputation: 1931

Not sure if this is actually answering my own question but i thought i'd give my final query from the awesome suggestions presented here.

with notes_cte (notes, account)
as
(
Select replace(CAST(Notes AS VARCHAR(MAX)),'  ',' '), Account_Id
FROM ....
where Notes like ('%needed%')
)

--select * from notes_cte

SELECT 

REVERSE(
SUBSTRING(
SUBSTRING(REVERSE(notes), PATINDEX('% dedeen%', REVERSE(notes))+11, 8000),
0,
PATINDEX('% %',  
  SUBSTRING(REVERSE(Notes), PATINDEX('% dedeen%', REVERSE(notes))+11, 8000)
 )
)
), account
FROM notes_cte
order by account

I had to add the REPLACE on the insert because I found some fields where people had inserted double spaces so I was getting blank results back. Once the REPLACE was added all good.

Upvotes: 0

Zak
Zak

Reputation: 461

There are a couple good answers here but this one might be easier to drop in your solution: http://sqlfiddle.com/#!3/930aa/35/0

SELECT 

REVERSE(
  SUBSTRING(
    SUBSTRING(REVERSE(Notes), PATINDEX('%dedeen %', REVERSE(Notes))+8, 8000),
    0,
    PATINDEX('% %',  
      SUBSTRING(REVERSE(Notes), PATINDEX('%dedeen %', REVERSE(Notes))+8, 8000)
     )
  )
)

Upvotes: 1

John
John

Reputation: 186

declare @junk varchar(100)
set @junk = 'Model 12340928435903284567 needed'
set @junk = 'Model 1234 needed'
set @junk = 'Hey, Model 948530498567304958634 needed'

select ltrim(rtrim(reverse(substring(ltrim(substring(reverse(@junk), patindex('dedeen', reverse(@junk)) + 7, 1000)), 1, CHARINDEX(' ', ltrim(substring(reverse(@junk), patindex('dedeen', reverse(@junk)) + 7, 1000)))))))

Comment out the different @junk assignments to see the different results.

Upvotes: 0

Lamak
Lamak

Reputation: 70678

Ok, this is ugly, but it works:

;WITH CTE AS
(
    SELECT *
    FROM (VALUES('Model 389448937594 needed'),
                ('Hey, Model 3453535345353453453 needed'),
                ('I demand 35435453 needed')) A(string)
)


SELECT  string,
        REVERSE(LEFT(Data,CHARINDEX(' ',Data))) Model
FROM (  SELECT  REVERSE(LEFT(string,PATINDEX('%needed%',string)-2)) Data,
                string
        FROM CTE) A

The results are:

╔═══════════════════════════════════════╦══════════════════════╗
║                string                 ║        Model         ║
╠═══════════════════════════════════════╬══════════════════════╣
║ Model 389448937594 needed             ║         389448937594 ║
║ Hey, Model 3453535345353453453 needed ║  3453535345353453453 ║
║ I demand 35435453 needed              ║             35435453 ║
╚═══════════════════════════════════════╩══════════════════════╝

Upvotes: 1

Related Questions