Subscribe to Subscribers

Resources » Code Snippets » SQL

Find the LENGTH of a string without using LEN Function in Sql Server


Posted Date:     Category: SQL    
Author: Member Level: Gold    Points: 5


This user-defined function will give you the length of the string.



This function make use of the SUBSTRING function to find the string length.


CREATE FUNCTION dbo.GetLength(@InputString VARCHAR(8000))
RETURNS INT
AS
/* -- Comments --
Created By: Alwyn Duraisingh.M
Created on: April 26 2010 10:10 PM
Purpose: To find the length of the string without using LEN() function
*/
BEGIN
DECLARE @Count INT, @Index INT, @Flag INT
SELECT @InputString = REPLACE(@InputString, ' ','*'),
@Count = 0, @Index = 1, @Flag = 1
--Looping through each and every character and count one by one
WHILE ( @Flag = 1 )
BEGIN
IF ((SELECT SUBSTRING(@InputString,@Index,1)) <> '')
BEGIN
SET @Count = @Count + 1
END
ELSE
BEGIN
SET @Flag = 0
END
SET @Index = @Index + 1
END
RETURN @Count
END


You can call the function like


SELECT dbo.GetLength('God is only one') AS LENGTH

RESULT:
*******
LENGTH
*******
15
Related Resources:


Read related articles: String length SQL    SQL functions    


Did you like this resource? Share it with your friends and show your love!


Responses to "Find the LENGTH of a string without using LEN Function in Sql Server"

No responses found. Be the first to respond...

Feedbacks      

Post Comment:




  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:   Sign In to fill automatically.
    Email: (Will not be published, but required to validate comment)



    Type the numbers and letters shown on the left.


    Next Resource: Pivoting INSERT - Oracle
    Previous Resource: Pagination using Stored Procedure
    Return to Resources
    Post New Resource
    Category: SQL


    Post resources and earn money!
     
    More Resources
    Popular Tags   Tag posting guidelines   Search Tags  
    Find length of the string without using LEN function  .  Sql Server String Length  .  

    Awards & Gifts
    Talk to Webmaster Tony John
    Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India
    2005 - 2013 All Rights Reserved.
    .NET and other trademarks mentioned in this site belong to Microsoft and other respective trademark owners.
    Articles, tutorials and all other content offered here is for educational purpose only.
    We are not associated with Microsoft or its partners.