Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

T-SQL 2005 String Pattern?? Expand / Collapse
Author
Message
Posted Wednesday, January 7, 2009 6:43 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 28, 2010 11:59 PM
Points: 141, Visits: 69
How to PRINT below string pattern in T-SQL 2005:

Input:
SET @str = 'HELLO WORLD'

Output:

H
HE
HEL
HELL
HELLO
HELLO
HELLO W
HELLO WO
HELLO WOR
HELLO WORL
HWLLO WORLD


Thanks in advance.
Post #632037
Posted Wednesday, January 7, 2009 6:58 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 1, 2014 9:49 PM
Points: 75, Visits: 409
declare @str varchar(20)
set @str = 'HELLO WORLD'

select SUBSTRING(@str, 1,n)
from Tally t
where N<=LEN(@str)

The 'Tally' table is simple table of numbers.... pls look for articles on Tally table here...
My Tally Table has only a single column called N.


How To Post
Post #632042
Posted Wednesday, January 7, 2009 7:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 12, 2014 10:11 PM
Points: 6, Visits: 245
Try this one:
Declare @str varchar(20),@i as int
Set @str='HELLOW WORLD'
Set @i=1
While @i <= len(@str)
Begin
print left(@str,@i)
Set @i=@i+1
End
Post #632051
Posted Wednesday, January 7, 2009 7:12 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 28, 2010 11:59 PM
Points: 141, Visits: 69
Thanks nil...prob solved!!
Post #632053
Posted Wednesday, January 7, 2009 7:18 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 1, 2014 9:49 PM
Points: 75, Visits: 409
Part of my point here is to turn the OP to the direction of tally tables also.
I purposely have asked him to look at them.

Here is a simple way to create a Tally Table (Shamelessly copied from Jeff's article).


--=============================================================================
-- Create and populate a Tally table
--=============================================================================

--===== Conditionally drop and create the table/Primary Key
IF OBJECT_ID('dbo.Tally') IS NOT NULL
DROP TABLE dbo.Tally

CREATE TABLE dbo.Tally
(N INT,
CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N))

--===== Create and preset a loop counter

DECLARE @Counter INT
SET @Counter = 1

--===== Populate the table using the loop and couner
WHILE @Counter <= 11000
BEGIN
INSERT INTO dbo.Tally
(N)
VALUES (@Counter)
SET @Counter = @Counter + 1
END

http://www.sqlservercentral.com/articles/TSQL/62867/


How To Post
Post #632055
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse