How to display a string vertically in SQL Server?

Below I have mentioned 2 queries using which you can display a string vertically:

Query 1: Using While loop:

DECLARE @string VARCHAR(256) = 'welcome'
DECLARE @cnt INT = 0;

WHILE(@cnt < len(@string))
BEGIN
    SET @cnt = @cnt + 1;
    PRINT SUBSTRING ( @string ,@cnt , 1 )
END;

Query 2: Using CTE(Common Table Expressions):

Declare @string varchar(10) ='welcome'
;with cte as
(
select 1 as i,substring(@string,1,1) as single_char
union all
select i+1 as i,convert(varchar(1),substring(@string,i+1,i+1)) as single_char from cte where
len(convert(varchar(1),substring(@string,i+1,i+1)))=1
)
select single_char From cte

Conclusion:

Above queries gives us a way how we can display a string vertically in Microsoft SQL Server.


How to insert different comma separated values into different columns in SQL Server?

Step 1: Create a table in which we will insert the CSV Data using the below query:

CREATE TABLE Test_Table(col1 NVARCHAR(200), col2 NVARCHAR(200), col3 NVARCHAR(200),col4 NVARCHAR(200),col5 NVARCHAR(200))

Step 2: Create an SP that will break the CSV string into columns and insert the data into the above created table as shown below:

ALTER PROC Test_SP
(
    @csvText NVARCHAR(MAX)
)
AS
BEGIN
DECLARE @status NVARCHAR(20) = ''
DECLARE @error_desc NVARCHAR(MAX)
DECLARE @csv_data TABLE
  (
    id INT NOT NULL IDENTITY(1, 1),
    csv_text NVARCHAR(MAX)
  )

INSERT INTO @csv_data(csv_text)
SELECT SUBSTRING([VALUE],1,LEN([VALUE])-1) FROM STRING_SPLIT(@csvText, CHAR(10)) WHERE LEN(VALUE) > 0


;WITH cte_csv_data AS (
    SELECT DISTINCT id
    , S.a.value('(/H/r)[1]', 'NVARCHAR(200)') AS col1
    , S.a.value('(/H/r)[2]', 'NVARCHAR(200)') AS col2
    , S.a.value('(/H/r)[3]', 'NVARCHAR(200)') AS col3
    , S.a.value('(/H/r)[4]', 'NVARCHAR(200)') AS col4
    , S.a.value('(/H/r)[5]', 'NVARCHAR(200)') AS col5
FROM
  (
    SELECT *,CAST (N'' + REPLACE(csv_text, ',', '') + '' AS XML) AS [vals]
    FROM @csv_data WHERE id > 0) d
    CROSS APPLY d.[vals].nodes('/H/r') S(a)
  )
INSERT INTO Test_Table
(
    col1,
    col2,
    col3,
    col4,
    col5
)
Select
    col1,
    col2,
    col3,
    col4,
    col5
FROM
    cte_csv_data
END
GO

Step 3: Let's execute the above created SP with CSV String as the parameter:

exec Test_SP 'surya, construction, 23, 2, 234443 
sandeep, IT, 20, 1, 455443'

Step 4: The output is as shown below:

How to insert different comma separated values into different columns in SQL Server?


How to split comma delimited string in SQL Server 2008?

Step 1: 

Create a function that will accept the "text string" and "character" where we want to split the string:

ALTER FUNCTION [dbo].[funSplit]
(
@Texto varchar(max),
@Splitter varchar(3)
)
RETURNS
@Lista TABLE
(
rowData varchar(8000)
)
AS
BEGIN
DECLARE @Pos Smallint
While len(@Texto)>0 BEGIN
  SET @Pos = Patindex('%'+@Splitter+'%',@Texto)
  
  IF @Pos > 0 BEGIN
   INSERT INTO @Lista
   SELECT left(@Texto, @Pos-1)
  
   SET @Texto = right(@Texto, len(@Texto)-@Pos)
  END
  ELSE BEGIN
   INSERT INTO @Lista
   SELECT @Texto
  
   SET @Texto = ''
  END
End
RETURN
END GO


Step 2: 

Now call the function pass the "sample string" & "character" to split as shown below:

select * from dbo.funSplit('sql-server-query-world','-')


The output of the query would look like below, that is our desired output:

How to split comma delimited string in SQL Server 2008?


How to update XML data in SQL Server 2008 using stored procedure?

Step 1:

Create a stored procedure that would take the input as XML data  & then break down the data to columns depending upon the XML tags formed:

CREATE PROC spExtractDataFromXML
(
 @inputXML XML=''
)
AS
BEGIN
SET NOCOUNT ON;
  SELECT
    x.i.value('./EmpId[1]','int') AS 'EmpId',
    x.i.value('./EmpName[1]','varchar(100)') AS 'EmpName',
    x.i.value('./Address[1]','varchar(100)') AS 'Address'
  FROM
  @inputXML.nodes('/Info/Data') AS x(i)
END
GO


Step 2:

Now call the stored procedure we created & pass the XML string as input to the SP:

Exec spExtractDataFromXML 
<Info>
 <Data>
    <EmpId>100</EmpId>
    <EmpName>ABC</EmpName>
    <Address>My Address</Address>
 </Data>
<Data>
    <EmpId>101</EmpId>
    <EmpName>ABC1</EmpName>
    <Address>My Address1</Address>
 </Data>
<Data>
    <EmpId>102</EmpId>
    <EmpName>ABC2</EmpName>
    <Address>My Address2</Address>
 </Data>
</Info>


The output of the query would look like below, that is our desired output:

saveXMLData


How to find duplicate characters in a string in SQL Server?

Below is the query that you can use in-order to get a character count from a string:

--Declared a table variable that will act as a temp table to hold the values

DECLARE @tbl_char TABLE (input VARCHAR(100),letter VARCHAR(1),remainder VARCHAR(100))

--SET the input & the character you want to search

DECLARE @Input VARCHAR(100)='suryapratapsingh', @characterToFind VARCHAR(1)='a', @remainder VARCHAR(100), @letter VARCHAR(100)=''
SET @remainder=@Input

--WHILE loop to iterate through all the remainder values of the string

WHILE(LEN(@remainder)>0)
BEGIN
  SELECT @Input=@Input, @letter=CONVERT(VARCHAR(1),LEFT(@remainder,1)), @remainder= RIGHT(@remainder, LEN(@remainder)-1)

  INSERT INTO @tbl_char(input,letter,remainder) VALUES (@Input,@letter,@remainder)
END

--Getting the count of the letter required by using a group by clause

SELECT Letter,count(1) AS 'Repeated_no_of_times' FROM @tbl_char
WHERE letter=@characterToFind
GROUP BY letter


Below is the output that we receive when we pass the inputstring=’suryapratapsingh’ and character_to_search=’a’:

findDuplicate characters in string Sql Server



How to shrink distribution database in SQL Server?

Fix the Retention days for Each Publication

Step 1:

Check for the publication properties subscription expiration setup. If it is set to "never expire" as you can see from the screen capture below.

shrinkDistribution db in sql server


Step 2:

Change the retention days to 120 hours and ran the above job, the distributor database space used was reduced from 30 GB to 300 MB.  After that I shrunk the database to 500 MB and then I re-indexed all tables in the distribution database and ran the clean up job again. Now, it takes less 3 sec to run the clean up job.  Also, replication as a whole is a lot faster.

You can in addition also do the below steps:

  • Setup an Alert to notify if any new publication is created where retention day is set to never expire
  • Implement additional replication monitoring
  • To shrink databases or database files look at DBCC SHRINKDATABASE and DBCC SHRINKFILE

How to insert dbnull in SQL Server?


Hello

First point that you need to keep in mind before you insert any null value in a SQL Server Table column is that you need to make the column to accept nulls by default which can be mentioned while creation of the Table by using the create table command as follows:

Example:

CREATE TABLE dbo.Students(Id INT IDENTITY(1,1) PRIMARY KEY, age INT default null, gender CHAR(1))
GO


Or if the table is created then Go-to the Database->TableName->Design->Column->Properties and set the default value as null-able.

Now if you send only those values that are to be inserted & has some value then those columns which has values will be inserted & the columns for which no values have been passed so by default NULL value will be inserted for those columns which are made null-able by default.

Hence, there is no need to pass null value from a front-end application to SQL Server it will automatically handle it at its end only pass the columns that have some value. 

Followers

Popular Posts