August 20, 2009 at 1:55 am
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 😉
August 20, 2009 at 2:02 am
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.
August 20, 2009 at 2:18 am
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
August 20, 2009 at 3:31 am
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
August 20, 2009 at 6:26 am
Dave Ballantyne (8/20/2009)
Select n+1from 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
August 20, 2009 at 8:27 am
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 😉
September 19, 2009 at 4:31 pm
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://qa.sqlservercentral.com/articles/T-SQL/62867/
--Jeff Moden
September 21, 2009 at 10:06 am
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/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply