The way to discover Size of String in SQL Server? LEN() Perform Instance

Some of the widespread process whereas writing SQL queries or saved process is to seek out the size of String. Since many of the columns are VARCHAR, you typically want to seek out the size earlier than taking any motion. In Java, you’ll find the size of String by utilizing the size() methodology however how about SQL Server? How will you discover the size of String in Microsoft SQL Server usually and Microsoft SQL Server 2016 specifically? Nicely, you should utilize the LEN() operate to seek out the size of a String worth in SQL Server, for instance, LEN(emp_name) gives you the size of values saved within the column emp_name. This methodology exists from SQL Server 2008 onwards which suggests you should utilize this operate in SQL Server 2012, 2014, 2016 and newest model of Microsoft SQL Server i.e. SQL Server 2017.

Btw, you need to keep in mind that that is totally different than the size of the particular column which you specify whereas creating desk e.g. emp_name VARCHAR(60).

To offer you an instance, you have got a column known as emp_name VARCHAR(60), which suggests the size of this column is 60 characters, it will probably maintain names with most 60 characters lengthy, however not all names are 60 characters lengthy, therefore chances are you’ll want to seek out the size of precise String at instances.

 On this SQL Server tutorial, I’ll educate you find out how to use the LEN() operate to seek out out precise size or variety of character in a VARCHAR or CHAR area.

LEN() operate Instance in SQL Server 2014

If you wish to discover out all names that are lower than 10 characters than you should utilize LEN() operate like following SQL question:

SELECT emp_name from Worker the place LEN(emp_name) < 10

This may return all worker whose title is lower than 10 character. LEN() is a really helpful methodology, it takes column title and returns the size of values saved in that column.

Let’s have a look at a few extra instance of LEN() operate in SQL Server 2008 and 2014:

1) Write an SQL question to seek out out all workers whose title is larger than 10 characters however lower than 50 character

SELECT title from Worker the place LEN(title) > 10 and LEN(title) < 50

You too can apply conditional logic utilizing a CASE assertion to do various things primarily based upon totally different size e.g. you’ll be able to shorten lengthy title by appending “…” on the finish of String.

SELECT CASE WHEN LEN (emp_name) <= 50 
THEN emp_name
ELSE LEFT(emp_name, 60) + '...'
END As emp_name
FROM Worker

So keep in mind to use LEN() operate in SQL Server to seek out out the size of any String saved in VARCHAR column. It does not must be VARCHAR, however LEN() operate accepts a textual content worth, which suggests it may very well be CHAR, VARCHAR, NCHAR or NVARCHAR as properly.

Associated SQL Server tutorials

Should you like this brief and candy tutorial and in search of some extra SQL SERVER gems then take a look at my following submit,  chances are you’ll like them as properly: