Silver Award WinnerSilver Award Winner

Sometimes i see in msdn forums 
and in real process problems about string columns

in this theme i will use Datalength() function 

Datalength()  function returns the length of the data
in the column in contrast to the length of the column

This function we can use in string and another (BLOB) type columns too

in this example I will divide the a long string with Datalength() function

first we will created table for using 

--first will created table
 
create table #array (k1 int identity,arraycol varchar(8000))
 
---now will inserted long value
 
insert #array(arraycol) values('LES PAUL '+
                               'BUDDY GUY '+
                               'JEFF BECK '+
                                 'JOE SATRIANI ')
insert #array(arraycol) values('STEVE NILLER '+
                                'EDDIE VAN HALEN '+
                                 'TOM SCHOLZ '+
                                 'JOE SATRIANI ')
insert #array(arraycol) values('STEVE VAI '+
                                'ERIC CLAPTON '+
                                 'SLASH '+
                                 'JIMI HENDRIX '+
                                 'JASON BECKER '+
                                  'MICHAEL HARTMAN')
 
---and select our example table
      
                 select*from #array
 
  k1    arraycol
1   LES PAUL BUDDY GUY JEFF BECK JOE SATRIANI
2   STEVE NILLER EDDIE VAN HALEN TOM SCHOLZ JOE SATRIANI
3   STEVE VAI ERIC CLAPTON SLASH JIMI HENDRIX JASON BECKER MICHAEL HARTMAN

and now i will use Datalength() function for divide long string column
update #array
set arraycol =
LEFT(arraycol,(3*15))+'MUDDY WATERS '+
RIGHT(arraycol,case when (datalength(arraycol)-(4*15))<0 then 0 else
datalength(arraycol)-(4*15) end )
where k1=2
select
      ELEMENT1=SUBSTRING(arraycol,(0*15)+1,15),
      ELEMENT2=SUBSTRING(arraycol,(1*15)+1,15),
      ELEMENT3=SUBSTRING(arraycol,(2*15)+1,15),
      ELEMENT4=SUBSTRING(arraycol,(3*15)+1,15),
      ELEMENT5=SUBSTRING(arraycol,(4*15)+1,15),
      ELEMENT6=SUBSTRING(arraycol,(5*15)+1,15)
      from #array
 
 
ELEMENT1    ELEMENT2    ELEMENT3    ELEMENT4    ELEMENT5    ELEMENT6
LES PAUL BUDDY  GUY JEFF BECK J OE SATRIANI            
STEVE NILLER ED DIE VAN HALEN T OM SCHOLZ JOE S MUDDY WATERS       
STEVE VAI ERIC  CLAPTON SLASH J IMI HENDRIX JAS ON BECKER MICHA EL HARTMAN 

and result was success