Extracting difference between strings t-sql

Question

How would I be able to extract the values 2011/01/01, 412 and 523 from @str1, knowing beforehand that it will have the format of @str2?

 declare @str1 varchar(100)
 declare @str2 varchar(100)
 set @str1 =  '2011/01/01 Creation of 412 Transactions and 523 Operations.'
 set @str2 = '{0} Creation of {1} Transactions and {2} Operations.'

Yes, see my own answer. Looking for something different to solve this. — dirtyw0lf, Nov 18 '16 at 18:40
string functions vary from db platform to platform. you need to tag you specific RDBMS, sql-server, oracle, mysql, etc? I would assume sql-server looking at your code in which case you just need to look at CHARINDEX() and SUBSTRING() — Matt, Nov 18 '16 at 18:41
Do we have a fixed number of parameters or at least a known maximum? — David דודו Markovitz, Nov 18 '16 at 19:13

John Cappelletti · Accepted Answer · 2016-11-18T19:36:17.083

Assuming SQL Server

Edit: I should add, if you can't use a UDF, it can easily migrated into the sub-query.

declare @str1 varchar(100) = '2011/01/01 Creation of 412 Transactions and 523 Operations.'
declare @str2 varchar(100) = '{0} Creation of {1} Transactions and {2} Operations.'

Select SeqNr = Row_Number() over (Order By A.RetSeq)-1
      ,Value = B.RetVal
  From (Select * from [dbo].[udf-Str-Parse](@str2,' ')) A
  Join (Select * from [dbo].[udf-Str-Parse](@str1,' ')) B
   on  A.RetSeq=B.RetSeq and A.RetVal Like '{%'

Returns

SeqNr   Value
0       2011/01/01
1       412
2       523

The UDF if needed

CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (  
    Select RetSeq = Row_Number() over (Order By (Select null))
          ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
    From (Select x = Cast('<x>'+ Replace(@String,@Delimiter,'</x><x>')+'</x>' as xml).query('.')) as A 
    Cross Apply x.nodes('x') AS B(i)
);
--Select * from [dbo].[udf-Str-Parse]('Dog,Cat,House,Car',',')
--Select * from [dbo].[udf-Str-Parse]('John Cappelletti was here',' ')

score 0 · Answer 2 · answered Nov 18 '16 at 18:48

 SELECT
    TheDate = CAST(LEFT(@str1,CHARINDEX(' ',@str1)) AS DATE)
    ,Transactions = CAST(
          SUBSTRING(@str1
             ,CHARINDEX('Creation of ',@str1) + LEN('Creation of ')
             ,CHARINDEX(' Transactions',@str1) - CHARINDEX('Creation of ',@str1) - LEN('Creation of ')
          ) AS INT)
    ,Operations = CAST(
          SUBSTRING(@str1
             ,CHARINDEX('Transactions and ',@str1) + LEN('Transactions and ')
             ,CHARINDEX(' Operations',@str1) - CHARINDEX('Transactions and ',@str1) - LEN('Transactions and ')
          ) AS INT)

thanks Mat. I am now looking for a broader way to solve this issue covering different cases. split string difference seems to be working well. — dirtyw0lf, Nov 18 '16 at 18:49

Deadsheep39 · Answer 3 · 2016-11-19T16:58:34.237

Not efficient but interesting way is replace "Creation of", "Transactions and" and "Operations." for xml elements tags, add tag on begining and use xquery (xpath) to qry xml. It could be very universal.

Or use patindex and substring functions for example into view:

create view vw_ParsedString
as
select
    str,
    substring(str, 1, 10) str_date,
    substring(str, 33, pidxToTran) str_trancnt,
    substring(str, pidxToTran + 17, pidxToOper) str_opercnt
from (
    select 
        str, 
        patindex('%Transactions and %', str) pidxToTran,
        patindex('%Operations.%', str) pidxToOper
    from tbl) pidx

10 is the length to end of date

33 is the length to first number

pidxToTran + 17 is the length to string + len of the string

This is very easy and effective, better than multiple use of scalar function. And when you compare with other script it is the most readable and shortest.

Extracting difference between strings t-sql

3 Answers3