CODEWITHSUNDEEP
sql
Edgedancer
Edgedancer

Reputation: 127

How to select the immediate characters BEFORE a specific string in SQL

So imagine I have a SQL tempTable with a text field with 2 pages worth of text in it.

select * from tempTable
pkid text
0 This is an example text with some images names like image1.svg or another one like image2.svg
1 This is another example text image3.svg and several images more like image4 and image5

What I want to know is if it's possible to select the characters before the .svg extension, so that the select result would look like

result
ike image1.svg
ike image2.svg
ext image3.svg

and so on. I've alread read about CHARINDEX and SUBSTRING, but I've only been able to find selects that return ALL text before my filter (.svg).

Upvotes: 2

Views: 147

Answers (2)

Edgedancer
Edgedancer

Reputation: 127

So I found a way to do it. This is the query I used using PATINDEX().

select pkid, SUBSTRING (text, PATINDEX('%.svg%',text)-60,65) 
from tempTable 
where text like '%.svg%'

This way you can either return ALL text before desired word/expression, or get a certain number of characters before, you just need to change the substring ranges.

Upvotes: 1

Jacob A.
Jacob A.

Reputation: 64

Here's what I came up with. This uses the string_split and LAG functions in MSSQL, but other database engines have similar features.

--create the temp table
DECLARE @temp AS TABLE (
 pkid int,
 text nvarchar(max)
)

--populate the temp table
INSERT INTO @temp (pkid, text) VALUES
(0, 'This is an example text with some images names like image1.svg or another one like image2.svg'),
(1, 'This is another example text image3.svg and several images more like image4 and image5')

--run the query to get the desired results
SELECT
    CONCAT(RIGHT(split.priorValue, 10), '.svg') AS result
FROM (
SELECT
    ss.value,
    LAG(ss.value, 1) OVER (ORDER BY pkid) AS priorValue
FROM @temp
CROSS APPLY string_split(text, '.') ss
) AS split
WHERE split.value LIKE 'svg%'

Upvotes: 0

Related Questions