• Home
  • SQL Server
    • Articles
    • T-SQL Puzzles
    • Output Puzzles
    • Interview Questions
    • Performance Tuning
    • SQL SERVER On Linux
    • Resources
  • SSRS
    • SSRS Articles
    • Interview Questions
  • SSAS
    • SSAS Articles
    • DAX
  • SQL Puzzles
  • Interview Questions
    • SQL Interview Questions
    • Data Interview Questions
  • Python Interview Puzzles
  • New Features(SQL SERVER)
    • SQL SERVER 2017
    • SQL SERVER 2016
    • SQL SERVER On Linux
  • Social
    • Expert Exchange
      • Top Expert in SQL
      • Yearly Award
      • Certifications
      • Achievement List
      • Top Expert of the Week
    • HackerRank (SQL)
    • StackOverflow
    • About Me
      • Contact Me
      • Blog Rules

Improving my SQL BI Skills

Improving my SQL BI Skills

Daily Archives: August 11, 2010

GENERATE A COMPLES RANDOM STRING IN SQL SERVER 2005

11 Wednesday Aug 2010

Posted by Pawan Kumar Khowal in SQL Concepts

≈ 2 Comments


GENERATE RANDOM STRING IN SQL SERVER 2005

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[aesp_GenerateRandomString] (
@useNumbers bit,
@useLowerCase bit,
@useUpperCase bit,
@charactersToUse as varchar(100),
@passwordLength as smallint,
@password varchar(30) OUTPUT
)
AS

BEGIN

DECLARE @characters varchar(100)
DECLARE @count int

IF @passwordLength <= 0
RaisError(‘Cannot generate a random string of zero length.’,16,1)

SET @characters = ”

IF @useNumbers = 1
BEGIN
— load up numbers 0 – 9
SET @count = 48
WHILE @count <=57
BEGIN
SET @characters = @characters + Cast(CHAR(@count) as char(1))
SET @count = @count + 1
END
END

IF @useLowerCase = 1
BEGIN
— load up uppercase letters A – Z
SET @count = 65
WHILE @count <=90
BEGIN
SET @characters = @characters + Cast(CHAR(@count) as char(1))
SET @count = @count + 1
END
END

IF @useUpperCase = 1
BEGIN
— load up lowercase letters a – z
SET @count = 97
WHILE @count <=122
BEGIN
SET @characters = @characters + Cast(CHAR(@count) as char(1))
SET @count = @count + 1
END
END

SET @count = 0
SET @password = ”

— IF you specIFy a character SET to use, the bit flags get ignored.
IF LEN(@charactersToUse) > 0
BEGIN
WHILE charindex(@charactersToUse,’ ‘) > 0
BEGIN
SET @charactersToUse = replace(@charactersToUse,’ ‘,”)
END

IF LEN(@charactersToUse) = 0
RaisError(‘Cannot use an empty character SET.’,16,1)

WHILE @count <= @passwordLength
BEGIN
SET @password = @password +
SUBSTRING(@charactersToUse,CAST(ABS(CHECKSUM(NEWID()))*RAND(@count) as
int)%LEN(@charactersToUse)+1,1)
SET @count = @count + 1
END
END
ELSE
BEGIN
WHILE @count <= @passwordLength
BEGIN
SET @password = @password + SUBSTRING(@characters,CAST(ABS(CHECKSUM(NEWID()))*RAND(@count) as int)%LEN(@characters)+1,1)
SET @count = @count + 1
END
END

SELECT @password

END
_________________________________________________
DECLARE @OutString VARCHAR(20)
exec dbo.aesp_GenerateRandomString 1,1,1,null,8,@OutString OUT
PRINT ‘Hi- ‘ + @OutString

______________________________________

The above stored procedure will give you a complex random sting every time.

Happy programming.Keep learning.

Pawan Kumar

Pawankkmr@hotmail.com

Share this

  • LinkedIn
  • Facebook
  • Twitter
  • WhatsApp
  • Email

CHECK WHETHER ALL CHARACTERS IN THE INPUT STRING ARE OF UPPER CASE OR NOT.

11 Wednesday Aug 2010

Posted by Pawan Kumar Khowal in SQL Concepts

≈ Leave a comment


CHECK WHETHER ALL CHARACTERS IN THE INPUT STRING ARE OF UPPER CASE OR NOT.
IF YES THEN RETURN 1
ELSE
RETURN -1

ALTER PROC CHECKSTRINGFORCAPTIALLETTERS
(
@str Varchar(250)
)
AS
BEGIN
DECLARE @return AS INT , @position AS INT , @MyValue INT
SET @position = 1
SET @return = -1
SET @MyValue = 0
WHILE @position <= DATALENGTH(@str)
BEGIN
IF ASCII(SUBSTRING(@str, @position, 1)) BETWEEN 65 AND 90
SET @return = 0
ELSE
SET @return = 1

SET @position = @position + 1
PRINT @return

IF @return = 1
BEGIN
SET @MyValue = -1
GOTO Himanshu
END
ELSE
BEGIN
SET @MyValue = 1
END
END
Himanshu: SELECT @MyValue
END

EXEC CHECKSTRINGFORCAPTIALLETTERS ‘HIMsNSHU’

Pawan Kumar
Pawankkmr@hotmail.com

Share this

  • LinkedIn
  • Facebook
  • Twitter
  • WhatsApp
  • Email

DELETE DUPLICATE ROWS FROM A TABLE IN SQL SERVER 2005 – LOGIC 2

11 Wednesday Aug 2010

Posted by Pawan Kumar Khowal in SQL Concepts

≈ Leave a comment


In my last article on delete duplicate rows i have used primary key to remove duplicate rows from the datatable

DELETE DUPLICATE ROWS FROM A TABLE IN SQL SERVER 2005 — Last article on duplicate rows

One can use the below query if the primary key is not there in the table.

SET ROWCOUNT 1

SELECT * FROM EMPLOYEE
WHILE @@ROWCOUNT > 0
DELETE FROM EMPLOYEE WHERE EMPNAME IN (SELECT DISTINCT EMPNAME FROM EMPLOYEE GROUP BY EMPNAME HAVING COUNT(*) > 1)

SET ROWCOUNT 0

Pawan Kumar

Pawankkmr@hotmail.com

Share this

  • LinkedIn
  • Facebook
  • Twitter
  • WhatsApp
  • Email

WAITFOR FUNCTION IN SQL SERVER 2005

11 Wednesday Aug 2010

Posted by Pawan Kumar Khowal in SQL Concepts

≈ Leave a comment


WAITFOR FUNCTION IN SQL SERVER 2005

It blocks the execution of a batch, stored procedure, or transaction until a specified time or time interval is reached,
or a specified statement modifies or returns at least one row.

Example of WAITFOR

WAITFOR ’00:00:10′
SELECT * FROM EMPLOYEE

GO

Notes:

1.WAITFOR will not work with Cursors and Views.

2.The actual time delay may vary from the time specified in
time_to_pass, time_to_execute, or timeout and depends on the activity
level of the server.

3.The time counter starts when the thread associated with the
WAITFOR statement is scheduled. If the server is busy, the thread may
not be immediately scheduled; therefore, the time delay may be longer than the specified time.

4.When the query exceeds the querywait option, the WAITFOR statement argument can complete without running.
You can check all active and waiting processes from sp_who.

5.Each WAITFOR statement creates a thread.

If many WAITFOR statements are specified on the same server, many threads can be tied up waiting for these statements to run.
SQL Server monitors the number of threads associated with WAITFOR
statements, and randomly selects some of these threads to exit if the
server starts to experience thread starvation.

Pawan Kumar
Pawankkmr@hotmail.com

Share this

  • LinkedIn
  • Facebook
  • Twitter
  • WhatsApp
  • Email

COALESCE STATEMENT TO CONCATENATE ALL THE ROWS OF A PARTICULAR COLUMN IN SQL SERVER 2005

11 Wednesday Aug 2010

Posted by Pawan Kumar Khowal in SQL Concepts

≈ Leave a comment


COALESCE STATEMENT TO CONCATENATE ALL THE ROWS OF A PARTICULAR COLUMN IN SQL SERVER 2005

–Create a table

CREATE TABLE EMPLOYEE
(
id INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
EMPNAME VARCHAR(100),
SALARY DECIMAL
)

–Insert some data in the above table

SELECT * FROM EMPLOYEE

OUTPUT
__________________

1 Pawan 180000
2 Gaurav 80000
3 Saurabh 80000
4 Pawan 830000
5 Neelam 200000

–Try out the below Query

DECLARE @EMPNAME VARCHAR(1024)
SELECT @EMPNAME = COALESCE(@EMPNAME + ‘ , ‘, ”) + LTRIM(RTRIM(EMPNAME)) FROM EMPLOYEE
PRINT @EMPNAME

OUTPUT
_____________________

Pawan , Gaurav , Saurabh , Pawan , Neelam.

ONE MORE ALTERNATIVE IS THERE

DECLARE @EMPNAME VARCHAR(1024)
SELECT @EMPNAME = ISNULL(@EMPNAME + ‘ , ‘, ”) + LTRIM(RTRIM(EMPNAME)) FROM EMPLOYEE
PRINT @EMPNAME

OUTPUT
_____________________

Pawan , Gaurav , Saurabh , Pawan , Neelam.

NOTE : COALESCE will return the thing in varchar2 that means is the string is too large then it will not work.

Pawan Kumar
Pawankkmr@hotmail.com

Share this

  • LinkedIn
  • Facebook
  • Twitter
  • WhatsApp
  • Email
← Older posts

Blog Stats

  • 1,074,524 hits

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,131 other subscribers

Pawan Khowal

502 SQL Puzzles with answers

Achievement - 500 PuzzlesJuly 18, 2018
The big day is here. Finally presented 500+ puzzles for SQL community.

200 SQL Server Puzzle with Answers

The Big DayAugust 19, 2016
The big day is here. Completed 200 SQL Puzzles today

Archives

August 2010
M T W T F S S
 1
2345678
9101112131415
16171819202122
23242526272829
3031  
« Jul   Sep »

Top Articles

  • pawankkmr.wordpress.com/2…
  • pawankkmr.wordpress.com/2…
  • pawankkmr.wordpress.com/2…
  • pawankkmr.wordpress.com/2…
  • pawankkmr.wordpress.com/2…

Archives

  • October 2020 (29)
  • September 2018 (2)
  • August 2018 (6)
  • July 2018 (25)
  • June 2018 (22)
  • May 2018 (24)
  • April 2018 (33)
  • March 2018 (35)
  • February 2018 (53)
  • January 2018 (48)
  • December 2017 (32)
  • November 2017 (2)
  • October 2017 (20)
  • August 2017 (8)
  • June 2017 (2)
  • March 2017 (1)
  • February 2017 (18)
  • January 2017 (2)
  • December 2016 (5)
  • November 2016 (23)
  • October 2016 (2)
  • September 2016 (14)
  • August 2016 (6)
  • July 2016 (22)
  • June 2016 (27)
  • May 2016 (15)
  • April 2016 (7)
  • March 2016 (5)
  • February 2016 (7)
  • December 2015 (4)
  • October 2015 (23)
  • September 2015 (31)
  • August 2015 (14)
  • July 2015 (16)
  • June 2015 (29)
  • May 2015 (25)
  • April 2015 (44)
  • March 2015 (47)
  • November 2012 (1)
  • July 2012 (8)
  • September 2010 (26)
  • August 2010 (125)
  • July 2010 (2)

Article Categories

  • Analysis Services (6)
    • DAX (6)
  • Data (2)
    • Data warehousing (2)
  • Integration Services (2)
  • Magazines (3)
  • Python (29)
  • Reporting Services (4)
  • SQL SERVER (820)
    • Download SQL Interview Q's (212)
    • SQL Concepts (323)
    • SQL Performance Tuning (155)
    • SQL Puzzles (331)
    • SQL SERVER 2017 Linux (6)
    • SQL Server Interview Questions (308)
    • SQL SERVER Puzzles (332)
    • T SQL Puzzles (547)
    • Tricky SQL Queries (439)
  • UI (30)
    • ASP.NET (5)
    • C# (13)
    • CSS (9)
    • OOPS (3)
  • Uncategorized (5)

Recent Posts

  • Python | The Print and Divide Puzzle October 30, 2020
  • Python | Count consecutive 1’s from a list of 0’s and 1’s October 30, 2020
  • Python | How to convert a number into a list of its digits October 26, 2020
  • Python | Validate an IP Address-IPV6(Internet Protocol version 6) October 26, 2020
  • Python | Print the first non-recurring element in a list October 26, 2020
  • Python | Print the most recurring element in a list October 26, 2020
  • Python | Find the cumulative sum of elements in a list October 26, 2020
  • Python | Check a character is present in a string or not October 26, 2020
  • Python | Check whether a string is palindrome or not October 26, 2020
  • Python | Find the missing number in the array of Ints October 26, 2020
  • Python | How would you delete duplicates in a list October 26, 2020
  • Python | Check whether an array is Monotonic or not October 26, 2020
  • Python | Check whether a number is prime or not October 26, 2020
  • Python | Print list of prime numbers up to a number October 26, 2020
  • Python | Print elements from odd positions in a list October 26, 2020
  • Python | Print positions of a string present in another string October 26, 2020
  • Python | How to sort an array in ascending order October 26, 2020
  • Python | How to reverse an array October 26, 2020
  • Python | Find un-common words from two strings October 26, 2020
  • Python | How to convert a string to a list October 26, 2020
  • Python | Find unique words from a string October 26, 2020
  • Python | Calculate average word length from a string October 26, 2020
  • Python | Find common words from two strings October 26, 2020
  • Python | Find the number of times a substring present in a string October 26, 2020
  • Python | Find maximum value from a list October 26, 2020
  • Python | How to find GCF of two numbers October 26, 2020
  • Python | How to find LCM of two numbers October 26, 2020
  • Python | How to convert a list to a string October 26, 2020
  • Python | Replace NONE by its previous NON None value October 26, 2020
  • Microsoft SQL Server 2019 | Features added to SQL Server on Linux September 26, 2018

Create a website or blog at WordPress.com

  • Follow Following
    • Improving my SQL BI Skills
    • Join 231 other followers
    • Already have a WordPress.com account? Log in now.
    • Improving my SQL BI Skills
    • Customize
    • Follow Following
    • Sign up
    • Log in
    • Report this content
    • View site in Reader
    • Manage subscriptions
    • Collapse this bar