Guruji Point - Code You Want To Write

  • Home
  • Asp.Net
  • WCF
  • SQL
  • Type Script
  • AngularJS
  • Tech News
  • Blog
  • Earn Online

Thursday, 11 May 2017

Split A String In Comma Separated Values In SQL Server

 break string into comma in sql, comma seperated string in sql, split string in sql server     1 comment   


Introduction

In this article we will learn about how to split a string in comma separated value from a specified range. Split string using Sub-string function and give a space after every comma. 

Previous Updates

In previous articles we have learnt Generate QR code for text also Read the QR code image and  Configure Setup(Installer) project in VS2015. If Else and Case statement  , While Loop . What is Cursor in sql and use of cursor. Difference between Row_Number(), Rank(), Rank_Density() with example.

Practice 
In SQL server many times we need split the given string into comma separated string. We can perform this task in many different different ways but here i am sharing the best and very convenient way. You can split your string from where you want , you are able to split whole string from a given no of period . Here is my SQL code for this :


DECLARE @BreakCount INT =5
DECLARE @STRINGVALUE VARCHAR(MAX) =              'ABCDEFGHIJKLMNOPQRSTUVWXYZ12345678900987654321ABCDEFGHIJ'

DECLARE @OutputValue VARCHAR(MAX)= ''
DECLARE @OutputValue1 VARCHAR(MAX)= NULL
DECLARE @LenOut INT = 1
DECLARE @LenOut1 INT = 1

DECLARE @Len INT ;
SELECT @Len = LEN(@StringVALUE)
SELECT @LenOut = 1
WHILE  @LenOut1 !=0
BEGIN

 SELECT @OutputValue = SUBSTRING(@STRINGVALUE, @LenOut, @BreakCount)
 SELECT @LenOut1 = LEN(@OutputValue) 
         IF (@LenOut1>0)
                BEGIN
                
                IF @OutputValue1 IS NULL 
                BEGIN
                       select @OutputValue1 = @OutputValue + ', '
                       SELECT @LenOut = @LenOut + LEN(@OutputValue)
                END
                ELSE
                BEGIN
                       select @OutputValue1 = @OutputValue1 + @OutputValue + ', '
                       SELECT @LenOut = @LenOut + LEN(@OutputValue)
                END
         END      
END

SELECT @OutputValue1 AS RESULT


Here BreakCount variable is for where you want to break the given string. I set the BreakCount =5 means it will break the sting after every five words and add comma and space then add new string.
Here is the output in Result window for above SQL statement :


If you set the BreakCount to 1 then the output look like this:


  • Share This:  
  •  Facebook
  •  Twitter
  •  Google+
  •  Stumble
  •  Digg
Email ThisBlogThis!Share to TwitterShare to Facebook
Newer Post Older Post Home

1 comment:

  1. for ict 991 August 2020 at 22:38

    The effectiveness of IEEE Project Domains depends very much on the situation in which they are applied. In order to further improve IEEE Final Year Project Domains practices we need to explicitly describe and utilise our knowledge about software domains of software engineering Final Year Project Domains for CSE technologies. This paper suggests a modelling formalism for supporting systematic reuse of software engineering technologies during planning of software projects and improvement programmes in Final Year Projects for CSE.

    Software management seeks for decision support to identify technologies like JavaScript that meet best the goals and characteristics of a software project or improvement programme. JavaScript Training in Chennai Accessible experiences and repositories that effectively guide that technology selection are still lacking.

    Aim of technology domain analysis is to describe the class of context situations (e.g., kinds of JavaScript software projects) in which a software engineering technology JavaScript Training in Chennai can be applied successfully

    The Angular Training covers a wide range of topics including Components, Angular Directives, Angular Services, Pipes, security fundamentals, Routing, and Angular programmability. The new Angular TRaining will lay the foundation you need to specialise in Single Page Application developer. Angular Training

    ReplyDelete
    Replies
      Reply
Add comment
Load more...

Facebook Updates

Guruji Point

Categories

comma seperated string in sql Comman table Expression in SQL Dynamic Grid Row IQueryable in c# Learn AngularJS row_number() and dense_rank() salary table structure. Scope_Identity() Use of indexes while loop in sql why do we need cursor why tuple used

About Us

This blog is about the Programming ,Tech News and some intresting facts related Contents. Here you can find fundamental and expert level topic of programming languages very practically related to C# , Asp.Net ,Sql-Server, JavaScript, Jquery, WebServices And also Web-Api, WCF ,WPF, Angular Js.

Contact Us

Email Us - gurujipoints@gmail.com
Contact- 8077657477

Reach Us

Copyright © Guruji Point - Code You Want To Write