Capture

SUBSTRING( Column, StartIndex, Length)

PATINDEX(Regex Pattern, Cell)

CONVERT(Data Type, Cell)

Use query

[syntax type=”js”]

SELECT PATINDEX(‘%”,”thumbnail%’,TmpImages), PATINDEX(‘%image%’,TmpImages), CONVERT(INT,(PATINDEX(‘%”,”thumbnail%’,TmpImages) – (PATINDEX(‘%image%’,TmpImages) + 8))),TmpImages,
SUBSTRING(TmpImages,PATINDEX(‘%image%’,TmpImages) + 8, CONVERT(INT,(PATINDEX(‘%”,”thumbnail%’,TmpImages) – (PATINDEX(‘%image%’,TmpImages) + 8))))
FROM dbo.SupplyItems
WHERE LEN(TmpImages) > 2

[/syntax]

Use CURSOR

[syntax type=”js”]DECLARE
@id BIGINT,
@imageLink NVARCHAR(MAX)
DECLARE complex_cursor CURSOR FOR
SELECT Id, SUBSTRING(TmpImages,PATINDEX(‘%image%’,TmpImages) + 8, CONVERT(INT,(PATINDEX(‘%”,”thumbnail%’,TmpImages) – (PATINDEX(‘%image%’,TmpImages) + 8))))
FROM dbo.SupplyItems
WHERE LEN(TmpImages) > 2
OPEN complex_cursor
FETCH NEXT FROM complex_cursor
INTO @id, @imageLink
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT CONVERT(NVARCHAR(MAX),@id) + ‘ ‘ + @imageLink
FETCH NEXT FROM complex_cursor
INTO @id, @imageLink
END
CLOSE complex_cursor;
DEALLOCATE complex_cursor;[/syntax]

SUBSTRING with PATINDEX, CONVERT in SQL Server

Category: DatabaseMSSQL Server
0
1797 views

Join the discussion

Your email address will not be published. Required fields are marked *