Convert the columns into a comma separated string in T-Sql

Convert the columns into a comma separated string in T-Sql

Input :-

test1

test2

test3

test4

Output:-           test1,test2,test3,test4

Query:-

SELECT STUFF((SELECT ', ' +convert(nvarchar, name) FROM (SELECT name FROM Locations_Alternative where LID=234) AS T FOR XML PATH('')),1,1,'') AS [Name]

Get all nested subcategories or data from a table in sql server

Consider the situation-
ID      Name                 PID
1       a                         0        
2       -a.a                     1
3       --a.a.a                2
4       --a.a.b               2
5       ---a.a.b.a           4
6       ---a.a.b.b           4
7       ----a.a.b.b.a       6
8       -a.b                   1
9       -a.c                   1
10      --a.c.a              9
11      --a.c.b              9
12      b                      0
13      -b.a                 12
14      -b.b                 12
15      --b.b.a             14

and you want to select all the subcategories to n level under any category i.e.
if you want that all subcategories under "b" will be selected -
Result-
12      b                       0
13      -b.a                  12
14      -b.b                  12
15      --b.b.a              14

or

All subcategories under category "a" -
result-
1       a                        0        
2       -a.a                    1
3       --a.a.a                2
4       --a.a.b               2
5       ---a.a.b.a           4
6       ---a.a.b.b           4
7       ----a.a.b.b.a      6
8       -a.b                   1
9       -a.c                   1
10      --a.c.a              9
11      --a.c.b              9

-- =============================================

CREATE PROCEDURE [dbo].[Sp_SubCategoryListings]

    -- Add the parameters for the stored procedure here

    @ID int

    AS

    BEGIN

    WITH childCatagories(oids,name, child_oids,RecursionLevel) AS

   (

    SELECT b.ID,b.Name, b.PID, 0 AS RecursionLevel

    FROM dbo.tbl_category AS b

   WHERE b.PID = @ID -- 'everything for the home' oid

   UNION ALL

   SELECT bom.ID, bom.Name,bom.PID, RecursionLevel + 1

   FROM dbo.tbl_category AS bom

   INNER JOIN childCatagories AS p

  ON bom.PID = p.oids

   )

  SELECT * FROM childCatagories AS p ORDER BY RecursionLevel

  OPTION (MAXRECURSION 1000);

  END

Avoid duplicate entry while using insert into .... select statement

 Avoid duplicate entry while using insert into .... select  statement

       Insert into Names

       select name.Value as name, abr.Value as abr 

       from  name

       INNER JOIN   abr ON name.RowID = abr.RowID

       where NOT EXISTS (select 1

                 from Names a

                 where a.name =name.Value

                 and a.abr = abr.Value)

Find and Delete duplicate records

---

Find Duplicate Records using SQL

       SELECT name, COUNT(name) AS namecount

       FROM tbl_content

       GROUP BY name

       HAVING ( COUNT(name) > 1) 

Delete Duplicate Records using SQL

       delete T1 from tbl_content T1, tbl_content T2 where T1.name = T2.name and T1.id > T2.id 

Delete Duplicate Records using Access

      delete from

      MyTable

      where uniqueField not in

      (select min(uniqueField) from

      MyTable T2

      where T2.dupField=MyTable.dupField)

Remove the string after a particular character(Making Sub string)

This code may be used where we want to remove a the string after a particular character.
Example :- Suppose your column have like this -

ID Name
1 MNIT,Jaipur
2 GVSET,JAipur
3 MSJ,Bharatpur etc.

Now you want to remove the cities name from this column.
You can do this by simply using this code -

        UPDATE Table_Name SET Name= dbo.substr(Name)

Function should be look like this-

     

     CREATE FUNCTION dbo.substr(@string nVARCHAR(MAX))

     RETURNS nVARCHAR(MAX)

         BEGIN     

             DECLARE @SpaceIndex TinyInt     

             SET @SpaceIndex = CHARINDEX(',', @string)

             if @SpaceIndex>0         

                 set @string=( LTRIM(RTRIM(LEFT(@string, @SpaceIndex - 1))))

             else

                 set @string=(LTRIM(RTRIM(@string))) 

             RETURN @string

        END

    GO

Call By Using Below Function-

        UPDATE Tbl_Content SET Name= dbo.substr(Name)

Remove Spaces in columns in sql table

Remove Spaces in columns in sql table

        CREATE FUNCTION dbo.TRIM(@string VARCHAR(MAX))
        RETURNS VARCHAR(MAX)
             BEGIN
                  RETURN LTRIM(RTRIM(@string))
             END
       GO

SELECT dbo.TRIM('    aaaaa strig   ')
or

      UPDATE Table_name SET columnname= dbo.TRIM(columnname))
or


WITH trimmed AS

(SELECT LTRIM(RTRIM(fld1)) as fld1, LTRIM(RTRIM(fld2)) as fld2 ....)

SELECT...

You can use following query for removing white spaces from all the columns




     UPDATE Table_nameSETcolumnname= REPLACE(columnname,' ',''),columnname1= 

     REPLACE(columnname1,' ','')

and so on for as many columns as you have.

CHARINDEX: Get index of the delimiting space

CHARINDEX: Get index of the delimiting space

.Net Framework has very useful String.IndexOf that returns position of first occurrence of given char or substring in string. Although there is no function named IndexOf in T-SQL you can achieve same result with CHARINDEX and PATHINDEX function.

CHARINDEX function as counterpart of IndexOf()

CHARINDEX function has the following syntax:

CHARINDEX(expression1, expression2 [, start_location])

expression1 - sequence of characters to be found
expression2 - column or expression that is searched
start_location - optional parameter, start position of searching

Example that search for first position of word "chart" in ProductDescription column could look like:

SELECT CHARINDEX('chart', ProductDescription) FROM Products

PATINDEX function

PATINDEX function works very similar to CHARINDEX. Unlike CHARINDEX, PATINDEX function has only two parameters and first parameter is pattern that can include wildcard parameters. Syntax is like this:

PATINDEX('%pattern%', expression)

pattern - string that optionally can contain wildcard characters (%)

expression - column or expression to be searched

Example that solves the same problem as above would be:

SELECT PATINDEX('chart', ProductDescription) FROM Products

CHARINDEX vs. PATINDEX

So, there are two solutions that simulate C# or VB.NET IndexOf() function. The logical question is, which is better and what is the difference between them?

CHARINDEX has three parameters and it is better choice if you need to specify search starting position.

PATINDEX supports patterns with wild characters _ or %, or match range with [ ] and [^ ] so you have much more options to define what you want to find.

How to simulate LastIndexOf() in T-SQL?

You can simulate LastIndexOf with CHARINDEX or PATINDEX combined with REVERSE function. REVERSE function, as the name suggests, returns reverse of character sequence. So LastIndexOf implementation example that finds last position of word "chart" in column ProductDescription would be:

SELECT CHARINDEX('chart', REVERSE(ProductDescription)) FROM Products

Using of CHARINDEX or PATINDEX as substitute for LIKE keyword

One more use of CHARINDEX and PATINDEX functions is to find whether or not given string exists in column or expression. Instead of LIKE keyword, you can use example like this that returns all rows that contain word "chart" in ProductDescription column:

SELECT * FROM Products WHERE CHARINDEX('chart', ProductDescription) > 0



Example -

DECLARE @FullName VarChar(25), @SpaceIndex TinyInt SET @FullName = 'www.java2s.com' -- Get index of the delimiting space: SET @SpaceIndex = CHARINDEX('java', @FullName)
-- Return all characters to the left of the space: SELECT LEFT(@FullName, @SpaceIndex - 1) GO

Output

-------------------------

(1 rows affected)