How to split a comma-separated value to columns in sql server (How to split a single column values to multiple column values? )

Syntax:
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mytable]') AND type in (N'U'))
DROP TABLE [dbo].[mytable]
GO
CREATE TABLE [dbo].[mytable](
[id] [int] NULL,
[FullName] [varchar](5000) NULL
) ON [PRIMARY]

GO

Insert into mytable values (1,'Test,King,Demo')


mytable:

Id     FullName
---  ----------------
1     Test,King,Demo


ResultTable:

To separate the comma delimited string into 3 columns

Id    FullName                  Name      Surname         Last
---   ------------                -------        -----------          ------
1     Test,King,Demo           Test        King            Demo



Method 1 :-

Use the CommonTableExpression(CTE)

-- Define the CTE expression name and column list.
;WITH Split_Names (ID,Name, xmlname)
AS
-- Define the CTE query.
(
    SELECT ID,
    Name,
    CONVERT(XML,'<Names><name>'
    + REPLACE(YourCommaSeparatedValueColumn,',', '</name><name>') + '</name></Names>') AS xmlname
      FROM YourTableName
)
-- Define the outer query referencing the CTE name.
 SELECT ID,   
 xmlname.value('/Names[1]/name[1]','varchar(100)') AS Name, 
 xmlname.value('/Names[1]/name[2]','varchar(100)') AS Surname,
 xmlname.value('/Names[1]/name[3]','varchar(100)') AS Last
 FROM Split_Names

Method 2:-

 SELECT DISTINCT id
, S.a.value('(/H/r)[1]', 'VARCHAR(100)') AS Name
, S.a.value('(/H/r)[2]', 'VARCHAR(100)') AS SurName
, S.a.value('(/H/r)[3]', 'VARCHAR(100)') AS Last
FROM
(
SELECT *,CAST (N'<H><r>' + REPLACE(fullname, ',', '</r><r>')  + '</r></H>' AS XML) AS [vals]
FROM mytable) d
CROSS APPLY d.[vals].nodes('/H/r') S(a)

unable to pass # character via querystring

To create a querystring with a # in it, from jquery, and read that value accurately in C# when the page loads



Use

encodeURIComponent()

before passing that.

Like:
var id="test # 232425 #454646"
"www.example.com?id=" + encodeURIComponent(id);
or just encode the entire url.

Result :
www.example.com?id=test # 232425 #454646";

How to get a list of database objects changed for specific time sql server

To get List of database objects changed for last 30 days from currect date.

SELECT type, name,create_date,modify_date

FROM    sys.objects

WHERE   modify_date >= DATEADD(DAY, -30, GETDATE())

order by [TYPE] asc

parse float with Comma Decimal Values

javascript's parseFloat doesn't take a locale parameter. So you will have to replace comma(,), dollar sign($) with empty

Example :
 Full Cost  Value : $1,500.00

convert the commas to Normal value(decimal value).

var fullcost = parseFloat($("#fullcost").text().replace('$', '').replace(',', '').replace(',', '.')); //1500.00

fullcost =parseFloat(1500.00)

Result value : 1500.00

The parseFloat() function parses a string and returns a floating point number.

This function determines if the first character in the specified string is a number. If it is, it parses the string until it reaches the end of the number, and returns the number as a number, not as a string.

Note: Only the first number in the string is returned!

Note: Leading and trailing spaces are allowed.

Note: If the first character cannot be converted to a number, parseFloat() returns NaN.

   

Common Table Expressions

Common Table Expressions are also called CTEs. This feature was introduced with SQL Server 2005. The CTE is preferred to use as an alternative to a Subquery/View.

Sub-queries

A sub-query is a query within a query. It is also called an inner query or a nested query. A sub-query is usually added in a where clause of the SQL statement.

Example
Select Name,Age, employeeID   
From table1   
Where employeeID in   
( 
   Select employeeID from table2 where salary >=7500 /******Sub Query******/ 
)

use a CTE

With aliastablename (column1) 
 
AS 
 
(Query)

example
With table1CTE(EmployeeID) 
 
AS   
(Select employeeID from table1 where salary >=7500)

Advantages
CTE improves the code readability.
CTE provides recursive programming.
CTE makes code maintainability easier.
Though it provides similar functionality as a view, it will not store the definition in metadata.

Find all tables containing column with specified name

select * from INFORMATION_SCHEMA.COLUMNS
where COLUMN_NAME like '%TableColumnName%'
order by TABLE_NAME


Select * from  INFORMATION_SCHEMA.COLUMNS
where COLUMN_NAME LIKE '%TableColumnName%'

How can I list all foreign keys referencing a given table in SQL Server?

EXEC sp_fkeys 'TableName'