SQL Server: Using Datalength() function

Sometimes we see in MSDN forums and in real process problems about string columns.

In this theme, we 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 strings and another (BLOB) type columns too.

In this example, we will divide a long string with Datalength() function.

First, we will create a 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

Now we will use Datalength() function for divide long string columns:

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