Sql Server 2005 Using Charindex() to Split a String

SQL Server 2005 Using CHARINDEX() To split a string

I wouldn't exactly say it is easy or obvious, but with just two hyphens, you can reverse the string and it is not too hard:

with t as (select 'LD-23DSP-1430' as val)
select t.*,
       LEFT(val, charindex('-', val) - 1),
   SUBSTRING(val, charindex('-', val)+1, len(val) - CHARINDEX('-', reverse(val)) - charindex('-', val)),
       REVERSE(LEFT(reverse(val), charindex('-', reverse(val)) - 1))
from t;

Beyond that and you might want to use split() instead.

SQL Server 2005 : split string into array and get array(x)?

If you know you will have exactly 4 columns, then you can also use this nested CTE version:

;with s1 (name, extra) as
(
  select left(data, charindex('/', data)-1), 
    substring(data, charindex('/', data) +1, len(data))
  from yourtable
),
s2 (name, surname, extra) as
(
  select name, 
    left(extra, charindex('/', extra)-1), 
    substring(extra, charindex('/', extra)+1, len(extra))
  from s1
),
s3 (name, surname, [character], company) as
(
  select name, 
    surname, 
    left(extra, charindex('/', extra)-1), 
    substring(extra, charindex('/', extra)+1, len(extra))
  from s2
)
select *
from s3;

See SQL Fiddle with Demo

The result is:

|  NAME | SURNAME | CHARACTER | COMPANY |
-----------------------------------------
| Peter |  Parker | Spiderman |  Marvel |
| Bruce |   Wayne |    Batman |      DC |

Or you can implement both a CTE that splits the data and then a PIVOT:

;with cte (item, data, colNum, rn) as
(
  select cast(left(data, charindex('/',data+'/')-1) as varchar(50)) item,
    stuff(data, 1, charindex('/',data+'/'), '') data,
    1 colNum,
    row_number() over(order by data) rn
  from yourtable
  union all
  select cast(left(data, charindex('/',data+'/')-1) as varchar(50)) ,
    stuff(data, 1, charindex('/',data+'/'), '') data,
    colNum+1, 
    rn
  from cte
  where data > ''
) 
select [1] as Name, 
  [2] as Surname, 
  [3] as [character], 
  [4] as company
from
(
  select item, colnum, rn
  from cte
) src
pivot
(
  max(item)
  for colnum in ([1], [2], [3], [4])
) piv

See SQL Fiddle with Demo

How do I split a delimited string so I can access individual items?

You may find the solution in SQL User Defined Function to Parse a Delimited String helpful (from The Code Project).

You can use this simple logic:

Declare @products varchar(200) = '1|20|3|343|44|6|8765'
Declare @individual varchar(20) = null

WHILE LEN(@products) > 0
BEGIN
    IF PATINDEX('%|%', @products) > 0
    BEGIN
        SET @individual = SUBSTRING(@products,
                                    0,
                                    PATINDEX('%|%', @products))
        SELECT @individual

        SET @products = SUBSTRING(@products,
                                  LEN(@individual + '|') + 1,
                                  LEN(@products))
    END
    ELSE
    BEGIN
        SET @individual = @products
        SET @products = NULL
        SELECT @individual
    END
END

How to split a string in T-SQL?

Use a table valued function like this,

CREATE FUNCTION Splitfn(@String varchar(8000), @Delimiter char(1))       
returns @temptable TABLE (items varchar(8000))       
as       
begin       
    declare @idx int       
    declare @slice varchar(8000)       

    select @idx = 1       
        if len(@String)<1 or @String is null  return       

    while @idx!= 0       
    begin       
        set @idx = charindex(@Delimiter,@String)       
        if @idx!=0       
            set @slice = left(@String,@idx - 1)       
        else       
            set @slice = @String       

        if(len(@slice)>0)  
            insert into @temptable(Items) values(@slice)       

        set @String = right(@String,len(@String) - @idx)       
        if len(@String) = 0 break       
    end   
return      

end

and get your variable and use this function like this,

SELECT i.items FROM dbo.Splitfn(@a,'|') AS i

SQL 2005 Split Comma Separated Column on Delimiter

Yes, it's possible with CROSS APPLY (SQL 2005+):

with testdata (CommaColumn, ValueColumn1, ValueColumn2) as (
  select 'ABC,123', 1, 2 union all
  select 'XYZ, 789', 2, 3
  ) 
select 
  b.items as SplitValue
, a.ValueColumn1
, a.ValueColumn2
from testdata a
cross apply dbo.Split(a.CommaColumn,',') b

Notes:

1. You should add an index to the result set of your split column, so that it returns two columns, IndexNumber and Value.

2. In-line implementations with a numbers table are generally faster than your procedural version here.

eg:

create function [dbo].[Split] (@list nvarchar(max), @delimiter nchar(1) = N',')
returns table
as
return (
  select 
    Number = row_number() over (order by Number)
  , [Value] = ltrim(rtrim(convert(nvarchar(4000),
        substring(@list, Number
        , charindex(@delimiter, @list+@delimiter, Number)-Number
        )
    )))
  from dbo.Numbers
  where Number <= convert(int, len(@list))
    and substring(@delimiter + @list, Number, 1) = @delimiter
  )

Erland Sommarskog has the definitive page on this, I think: http://www.sommarskog.se/arrays-in-sql-2005.html

How to split string using delimiter char using T-SQL?

For your specific data, you can use

Select col1, col2, LTRIM(RTRIM(SUBSTRING(
    STUFF(col3, CHARINDEX('|', col3,
    PATINDEX('%|Client Name =%', col3) + 14), 1000, ''),
    PATINDEX('%|Client Name =%', col3) + 14, 1000))) col3
from Table01

EDIT - charindex vs patindex

Test

select col3='Clent ID = 4356hy|Client Name = B B BOB|Client Phone = 667-444-2626|Client Fax = 666-666-0151|Info = INF8888877 -MAC333330554/444400800'
into t1m
from master..spt_values a
cross join master..spt_values b
where a.number < 100
-- (711704 row(s) affected)

set statistics time on

dbcc dropcleanbuffers
dbcc freeproccache
select a=CHARINDEX('|Client Name =', col3) into #tmp1 from t1m
drop table #tmp1

dbcc dropcleanbuffers
dbcc freeproccache
select a=PATINDEX('%|Client Name =%', col3) into #tmp2 from t1m
drop table #tmp2

set statistics time off

Timings

CHARINDEX:

 SQL Server Execution Times (1):
   CPU time = 5656 ms,  elapsed time = 6418 ms.
 SQL Server Execution Times (2):
   CPU time = 5813 ms,  elapsed time = 6114 ms.
 SQL Server Execution Times (3):
   CPU time = 5672 ms,  elapsed time = 6108 ms.

PATINDEX:

 SQL Server Execution Times (1):
   CPU time = 5906 ms,  elapsed time = 6296 ms.
 SQL Server Execution Times (2):
   CPU time = 5860 ms,  elapsed time = 6404 ms.
 SQL Server Execution Times (3):
   CPU time = 6109 ms,  elapsed time = 6301 ms.

Conclusion

The timings for CharIndex and PatIndex for 700k calls are within 3.5% of each other, so I don't think it would matter whichever is used. I use them interchangeably when both can work.

How to split strings in SQL Server

SELECT substring(data, 1, CHARINDEX(',',data)-1) col1,
substring(data, CHARINDEX(',',data)+1, LEN(data)) col2
FROM table

SQL Server : split string in SELECT statement

Surprisingly CAST('2/November/2000' as datetime) works (checked on SQL Server 2008), gives value 2000-11-02 00:00:00.000

SELECT  
Month(CAST(Account.date AS DateTime)) "Month",
Day(CAST(Account.date AS DateTime)) "Day",
Year(CAST(Account.date AS DateTime)) "Year",

FROM Account

But as rightly pointed out in comment how do you know if "02/11/2000" is November 2, 2000 or February 11, 2000?

Also the spelling of Month names must be absolutely correct else conversion fails. Since you are storing dates as string there is chance that entry like November , Agust etc could have been made .

You should never store date values as strings.

Need SQL trigger to split string based on dash delimeter

To separate the values you can use dbo.SplitStrings_CTE function

CREATE FUNCTION dbo.SplitStrings_CTE(@List nvarchar(max), @Delimiter nvarchar(1))
RETURNS @returns TABLE(val nvarchar(max), [level] int, PRIMARY KEY CLUSTERED([level]))
AS
BEGIN
;WITH cte AS
 (
  SELECT SUBSTRING(@List, 0, CHARINDEX(@Delimiter, @List)) AS val,
         CAST(STUFF (@List + @Delimiter, 1, CHARINDEX(@Delimiter, @List), '') AS nvarchar(max)) AS stval,
         1 AS [level]
  UNION ALL
  SELECT SUBSTRING(stval, 0, CHARINDEX(@Delimiter, stval)),
         CAST(STUFF (stval, 1, CHARINDEX(@Delimiter, stval), '') AS nvarchar(max)),
         [level] + 1
  FROM cte
  WHERE stval != ''
  )
  INSERT @returns
  SELECT REPLACE(val, ' ', '') AS val, [level]
  FROM cte
  RETURN
END

After creating a function, use this UPDATE statement

;WITH cte AS
 (    
  SELECT t.OriginalFile, t.EmployeeID, t.EmployeeTitle, t.Location, t.ApplicationID, 
         NewEmployeeID = MAX(CASE WHEN o.level = 1 THEN o.val END) OVER(),
         NewEmployeeTitle = MAX(CASE WHEN o.level = 2 THEN o.val END) OVER(),
         NewLocation = MAX(CASE WHEN o.level = 3 THEN o.val END) OVER(),
         NewApplicationID = MAX(CASE WHEN o.level = 4 THEN o.val END) OVER()
  FROM dbo.UploadAppTable t CROSS APPLY dbo.SplitStrings_CTE(t.OriginalFile, '-') o  
  )
  UPDATE cte
  SET EmployeeID = NewEmployeeID,
      EmployeeTitle = NewEmployeeTitle,
      Location = NewLocation,
      ApplicationID = NewApplicationID

Demo on SQLFiddle

OR UPDATE statement for a multiple update

;WITH cte AS
 (    
  SELECT t.OriginalFile, t.EmployeeID, t.EmployeeTitle, t.Location, t.ApplicationID, 
         NewEmployeeID = MAX(CASE WHEN o.level = 1 THEN o.val END) OVER(PARTITION BY t.OriginalFile),
         NewEmployeeTitle = MAX(CASE WHEN o.level = 2 THEN o.val END) OVER(PARTITION BY t.OriginalFile),
         NewLocation = MAX(CASE WHEN o.level = 3 THEN o.val END) OVER(PARTITION BY t.OriginalFile),
         NewApplicationID = MAX(CASE WHEN o.level = 4 THEN o.val END) OVER(PARTITION BY t.OriginalFile)
  FROM dbo.UploadAppTable t CROSS APPLY dbo.SplitStrings_CTE(t.OriginalFile, '-') o  
  )
  UPDATE cte
  SET EmployeeID = NewEmployeeID,
      EmployeeTitle = NewEmployeeTitle,
      Location = NewLocation,
      ApplicationID = NewApplicationID

Demo on SQLFiddle

---

Related Topics