selecting specific amount of strings

Question

Post reply

selecting specific amount of strings

StuDev

Old Hand

Points: 340
More actions
August 20, 2009 at 1:55 am

#214016

Hi friends,
How can i select a specific amount of string from a column?
i tried :
SELECT SUBSTRING(eventText, 1, 1) AS Expr1
FROM testing
Bud this returns the first character of the first string 😉

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

Sorry , but you are going to have to provide more detail ? I really dont understand what you want.

Please provide test data and an example of the data you need returned.

Clear Sky SQL
My Blog[/url]

StuDev Old Hand Points: 340 More actions · Answer 2

Hi SSC,

Suppose that i have the following record in my table:

SqlServerCentral website is a great help for me.

Now suppose that i want to select only 2 words from it: SqlServerCentral website

I tried :

SELECT SUBSTRING(eventName, 1, 2)

FROM testing

bud im getting only the first 2 chars : Sq

I hope it's clear now

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 3

What makes you think that substring returns words ? It returns characters ?

Try This

declare @MyString varchar(255)

select @MyString ='one two three four five'

;with Num1 (n) AS (SELECT 1 UNION ALL SELECT 1),

Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),

Num3 (n) AS (SELECT 1 FROM Num2 AS X, Num2 AS Y),

Num4 (n) AS (SELECT 1 FROM Num3 AS X, Num3 AS Y),

Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num4),

SpacesPos(sIndex)

as

(

Select n+1

from nums

where n spacesPos.SIndex)

from spacesPos

)

Select substring(@MyString,StartPos,EndPos-StartPos)

from cteSpaceDelta

where EndPos is not null

Clear Sky SQL
My Blog[/url]

Jeff Moden SSC Guru Points: 1004361 More actions · Answer 4

Dave Ballantyne (8/20/2009)
Select n+1
from nums
where n < datalength(@MyString)
and substring(@MyString,n,1)=' '
union
select 1
union
Select datalength(@MyString)+1

Now... that's an interesting, different, and simple way to isolate the delimiters... and I'm not talking about the use of a numbers/Tally table. Thanks, Dave... I've got some testing to do. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 5

Jeff Moden (8/20/2009)
Now... that's an interesting, different, and simple way to isolate the delimiters... and I'm not talking about the use of a numbers/Tally table. Thanks, Dave... I've got some testing to do. 🙂

Let me know how that goes 😉

Clear Sky SQL
My Blog[/url]

Jeff Moden SSC Guru Points: 1004361 More actions · Answer 6

Well, shoot... I thought you might have been onto something, Dave. I did have to make some very minor changes to your code so it could handle CSV's with a length of more than 256 and to get rid of trailing delimiters. I also changed the delimeter from a space to a comma for the test so we could see where they ended up. Here's the code I used to test with...

[font="Courier New"]--===== Declare the local variables for the test

DECLARE @MyString VARCHAR(8000), --Large CSV result

@NumElements INT, --Number of elements in the CSV

@ElementRange INT, --Range of numbers in the CSV

@ElementOffSet INT --Minimum starting number in the range

--===== Presets

SELECT @NumElements = 800,

@ElementRange = 1000000000,

@ElementOffSet = 1

--===== Create the large CSV according to the presets

SELECT @MyString = ISNULL(@MyString+',','')

+ CAST(ABS(CHECKSUM(NEWID()))%@ElementRange+@ElementOffSet AS VARCHAR(10))

FROM dbo.Tally t

WHERE t.N <= @NumElements

--===== Display the created CSV for this run

SELECT @MyString

--===== Test the two methods

PRINT REPLICATE('=',100)

PRINT '========== Dave''s Method =========='

SET STATISTICS TIME ON

;with Num1 (n) AS (SELECT 1 UNION ALL SELECT 1), --2

Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y), --4

Num3 (n) AS (SELECT 1 FROM Num2 AS X, Num2 AS Y), --16

Num4 (n) AS (SELECT 1 FROM Num3 AS X, Num3 AS Y), --256

Num5 (n) AS (SELECT 1 FROM Num4 AS X, Num4 AS Y), --65536 --Added to make the numbers large enough

Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num5)

,

SpacesPos(sIndex)

as

(

Select n+1

from nums

where n < datalength(@MyString)

and substring(@MyString,n,1)=',' --Changed to comma instead of space

union

select 1

union

Select datalength(@MyString)+2 --Changed to 2 to pick up last character of last element

)

,

cteSpaceDelta(StartPos,EndPos)

as

(

Select spacesPos.sIndex, --Add "-1" below to keep from picking up the trailing comma

NextsIndex = (Select Top 1 sIndex from SpacesPos as InnerSpaces where InnerSpaces.SIndex > spacesPos.SIndex)-1

from spacesPos

)

Select substring(@MyString,StartPos,EndPos-StartPos)

from cteSpaceDelta

where EndPos is not null

SET STATISTICS TIME OFF

PRINT REPLICATE('=',100)

PRINT '========== Optimized Tally Table Method =========='

SET STATISTICS TIME ON

SELECT SUBSTRING(@MyString,t.N,CHARINDEX(',',@MyString+',',t.N)-t.N) AS Element

FROM dbo.Tally t

WHERE t.N <= LEN(@MyString)+1

AND SUBSTRING(','+@MyString,t.N,1) = ','

SET STATISTICS TIME OFF[/font]

And, here's the results compared to a Tally table splitter...

(1 row(s) affected)
====================================================================================================
========== Dave's Method ==========

(800 row(s) affected)

SQL Server Execution Times:
   CPU time = 4203 ms,  elapsed time = 4575 ms.
====================================================================================================
========== Optimized Tally Table Method ==========

(800 row(s) affected)

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 43 ms.

As a side bar, for those interested in what a Tally table is and how it's used to replace While Loops in many cases, please see the following article...

http://www.sqlservercentral.com/articles/T-SQL/62867/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Britt Cluff SSCertifiable Points: 5083 More actions · Answer 7

A quick and dynamic solution would be:

create table #testing (eventname varchar(1000))

insert #testing (eventName) VALUES ('SqlServerCentral website is a great help for me.')

insert #testing( eventname) values ('this is a test - SQLServerCentral web')

SELECT SUBSTRING(eventName, CHARINDEX('SQLServerCentral web', eventName), LEN('SQLServerCentral web'))

FROM #testing

Which will return 2 records.

http://brittcluff.blogspot.com/