CODEWITHSUNDEEP
sqlsql-servert-sql
Reinardus Hans
Reinardus Hans

Reputation: 87

SQL query to get specific string between characters

I have output column named 'data' from command prompt to list files information like this example :

15/07/2020  14:31         1.395 image1.jpeg

I need query to get file name, file size, and extension to insert into a table column.

The expected result :

file    |   size   |   extension
image1  |   1395   |    jpeg

I've come up with this query to get the extension :

UPDATE table
SET extension = RIGHT(data, charindex('.', REVERSE(data)) - 1)

Can anyone help me how to get the file name and the file size? Thanks for the help.

Upvotes: 0

Views: 118

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67341

One type and position safe splitting calls JSON (needs v2016+) for the rescue:

DECLARE @YourString VARCHAR(100)='15/07/2020  14:31         1.395 image1.jpeg';

SELECT *
FROM OPENJSON(CONCAT('[["',REPLACE(REPLACE(LTRIM(SUBSTRING(@YourString,18,1000)),' ','.'),'.','","'),'"]]')) 
     WITH(Number INT           '$[0]'
         ,Size   INT           '$[1]'
         ,[Name] NVARCHAR(150) '$[2]'
         ,Ext    NVARCHAR(150) '$[3]'
         )A;

The idea in short:

  • Using REPLACE(LTRIM(SUBSTRING(@YourString,18,1000)),' ','.') cuts away the leading date and time and the spaces.
  • We switch the last remaining blank to a dot (result: 1.395.image1.jpeg).
  • Now we use some replacements to get a JSON array (result [["1","395","image1","jpeg"]])
  • We use double brackets to allwo for a WITH clause, where the array is within another array.
  • Now we can use the position (zero based) to fetch each item and return it typed and named (implicit pivoting).

Hint: If you are below v2016 you can do the same with XML:

SELECT TheXml.value('x[1]','int') AS Number
      ,TheXml.value('x[2]','int') AS Size
      ,TheXml.value('x[3]','nvarchar(150)') AS [Name]
      ,TheXml.value('x[4]','nvarchar(150)') AS Ext
FROM (VALUES(CAST('<x>' + REPLACE(REPLACE(LTRIM(SUBSTRING(@YourString,18,1000)),' ','.'),'.','</x><x>') + '</x>'  AS XML))) A(TheXml)

Upvotes: 1

vvvv4d
vvvv4d

Reputation: 4095

You can use SPLIT_STRING to break up the string to individual values and then parse out and identify the values you need using a case statement and finally then pivot the rows to columns so its easy to insert each specific value.

select
    date, [file], size
from (
select
    case 
        when value like '%/%/%' then 'date' 
        when ISNUMERIC(value) = 0 and value like '%.%' then 'file'
        when ISNUMERIC(value) = 1  and value like '%.%' then 'size'
        else 'na' end type,value
from (select value from STRING_SPLIT ( '15/07/2020  14:31         1.395 image1.jpeg' , ' ' ) ) d
) src
pivot
(
    max(value)
    for type in ([date], [file], [size])
) as piv

enter image description here

https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15 https://learn.microsoft.com/en-us/sql/t-sql/functions/isnumeric-transact-sql?view=sql-server-ver15 https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15

Upvotes: 0

Related Questions