0

Here is an example:

[U_TipTon]=118.7->59.35;[U_Haulge]=428.28->214.14

I need to extract just 118.7->59.35 as Tipton, and 428.28->214.14 in another column as U_Haulage.

The length of the string is variable as well as the posision os my pattern word.

I am trying with Patindex but I cannot find the way.

Ana Docampo
  • 1

2 Answers2

1

In MySQL there's SUBSTRING_INDEX, which extracts a substring based on a delimiter:

select
  substring_index(substring_index(x, '[U_TipTon]=', -1), ';', 1) as TipTon
  ,substring_index(substring_index(x, '[U_Haulge]=', -1), ';', 1) as Haulge
from
 (
   select '[U_TipTon]=118.7->59.35;[U_Haulge]=428.28->214.14' as x
 ) as dt

Edit: In MS SQL Server it's more complicated:

select 
   substring(xHaulge, 1, charindex(';', xHaulge + ';')-1) as Haulge,
   substring(xTipTon, 1, charindex(';', xTipTon + ';')-1) as TipTon
from
 (
   select
      case when charindex('[U_Haulge]=', x) > 0 
           then substring(x, charindex('[U_Haulge]=', x) + len('[U_Haulge]='), 8000)
           else '' 
      end as xHaulge,
      case when charindex('[U_TipTon]=', x) > 0 
           then substring(x, charindex('[U_TipTon]=', x) + len('[U_TipTon]='), 8000) 
           else '' 
      end as xTipTon
   from 
    (
      select '[U_TipTon]=118.7->59.35;[U_Haulge]=428.28->214.14' as x
    ) as dt
 ) as dt
dnoeth
  • 59,503
  • 4
  • 39
  • 56
  • The idea would work for 1 specif row, but I have 4000 row, in which the position of this words, the numbers and length are different in each row. – Ana Docampo Feb 16 '15 at 11:28
  • Did you actually try my query on your data? It works exactly as requested as long as the name/value pairs are separated by semicolons.. – dnoeth Feb 16 '15 at 11:58
  • As I am using SQL server and no mySQL don't know what function use to substitute substring_index – Ana Docampo Feb 16 '15 at 12:16
  • Why do you tag the query with mysql? And SQL is **not** MS SQL Server, it's generic Standard SQL. What's your SQL Server release? – dnoeth Feb 16 '15 at 13:06
  • Added a solution for SQL Server – dnoeth Feb 16 '15 at 14:00
0

The solution was:

case when charindex('Haulge',t.xField) > 0 then

substring( t.xField , charindex('Haulge',t.xField) + 8,case when charindex(';',substring( t.xField , charindex('Haulge',t.xField) + 8,LEN(t.xField))) = 0 then LEN(t.xField) else charindex(';',substring( t.xField , charindex('Haulge',t.xField) + 8,LEN(t.xField)))-1 end ) else '-' end [Haul Price] ,case when charindex('Tipton',t.xField) > 0 then

substring( t.xField , charindex('TipTon',t.xField) + 8,case when charindex(';',substring( t.xField , charindex('Tipton',t.xField) + 8,LEN(t.xField))) = 0 then LEN(t.xField) else charindex(';',substring( t.xField , charindex('Tipton',t.xField) + 8,LEN(t.xField)))-1 end ) else '-' end [Tip Price] ,case when charindex('AItmPr',t.xField) > 0 then

substring( t.xField , charindex('AItmPr',t.xField) + 8,case when charindex(';',substring( t.xField , charindex('AItmPr',t.xField) + 8,LEN(t.xField))) = 0 then LEN(t.xField) else charindex(';',substring( t.xField , charindex('AItmPr',t.xField) + 8,LEN(t.xField)))-1 end ) else '-' end [Additional Price]

Ana Docampo
  • 1