SQL Server How to capture a string starting with a specific string and stop after any space

lmyy7pcs 于 2023-05-05 发布在其他

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

I am trying to capture a specific word within a column. I want to extract the word that starts with 'lat'. Basically would like to capture latship , latprod, latdfu
| JobName |
| ------------ |
| pl_blob_consolidated - latship |
| pl_blob - latprod |
| pl_blob - latdfu - final |
| pl_blob - latdfu (final_blob) |
| pl_blob - latdfu - final_blob - sql |

DECLARE @tbl TABLE (id INT IDENTITY PRIMARY KEY, JobName VARCHAR(MAX));

INSERT INTO @tbl (JobName) VALUES
('pl_blob_consolidated - latship'),
('pl_blob - latprod'),
('pl_blob - latdfu - final'),
('pl_blob - latdfu (final_blob)'),
('pl_blob - latdfu - final_blob - sql');

Sample output:

JobName
latship
latprod
latdfu
latdfu
latdfu

select DISTINCT   SUBSTRING(JobName, CHARINDEX('lat', JobName)
     , LEN(JobName) - CHARINDEX('lat', JobName) + 1) 
     , LEN(JobName) - CHARINDEX('lat', JobName) + 1 
 from tableName

Tried this, but won't stop after the word ends.

Thanks

sql-server

来源：https://stackoverflow.com/questions/76148777/how-to-capture-a-string-starting-with-a-specific-string-and-stop-after-any-space

2条答案

按热度按时间

3ks5zfa01#

you can try this with some regular expressions (SQL Server function PATINDEX) : column name myField, table myTable:

SELECT 
/* index of first occurence of lat */
  SUBSTRING(myField, PATINDEX('%lat%', myField), 
/* up to index of first not char or digit or _  */
    PATINDEX('%[^a-zA-Z0-9_]%',         
/* into the string after lat.  (+3 to be after t, up to the end) */
     SUBSTRING(myField, PATINDEX('%lat%', myField) + 3, LEN(myField))) - 1) 
 AS extracted_word
FROM myTable
WHERE myField LIKE '%lat%'

赞(0）回复(0）举报 2023-05-05

n3h0vuf22#

Please try the following solution.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (id INT IDENTITY PRIMARY KEY, JobName VARCHAR(MAX));
INSERT INTO @tbl (JobName) VALUES
('pl_blob_consolidated - latship'),
('pl_blob - latprod'),
('pl_blob - latdfu - final'),
('pl_blob -> latdfu - final_blob'),
('pl_blob - latdfu - final_blob - sql');
-- DDL and sample data population, end

SELECT t.*
    , result = TRIM(value) 
FROM @tbl AS t
    CROSS APPLY STRING_SPLIT(REPLACE(JobName,'->','-'), '-')
WHERE TRIM(value) LIKE 'lat%';

Output

id	JobName	result
1	pl_blob_consolidated - latship	latship
2	pl_blob - latprod	latprod
3	pl_blob - latdfu - final	latdfu
4	pl_blob -> latdfu - final_blob	latdfu
5	pl_blob - latdfu - final_blob - sql	latdfu

赞(0）回复(0）举报 2023-05-05

我来回答

SQL Server How to capture a string starting with a specific string and stop after any space

2条答案

相关问题

热门标签

最新问答