May 25, 2009 at 10:30 am
Hello guys
Any idean on how can i separate this word using substring or charindex or any approach will do
Example Johnson, Newyork
Here i need to separate JohnSon from newyork johnson will be put in column A while Newyork will be put in Column B, but if there is no Comma(,) in the word column be should be palace as empty sting.
Any idea?..
Please help me asap..
Thanks
May 25, 2009 at 10:55 am
Hey,
surely that is a charindex and substring question? Scratching my head trying to use one or the other...
DECLARE @String VARCHAR(64)
SELECT @String = 'Johnson Newyork'
select CHARINDEX(',', @String)
SELECT
SUBSTRING(@String, 1 ,
CASE CHARINDEX(',', @String)
WHEN 0
THEN LEN(@STRING)
ELSE CHARINDEX(',', @String) - 1
END
),
CASE CHARINDEX(',', @String)
WHEN 0
THEN ''
ELSE LTRIM(SUBSTRING(@String, CHARINDEX(',', @String) + 1, LEN(@String)))
END
/// Edited for empty case
May 25, 2009 at 11:10 am
Bugger, missed the empty case!
Here is Jeff's more elegant solution (full code found at http://www.sqlservercentral.com/articles/T-SQL/62867/
--=============================================================================
-- Setup
--=============================================================================
USE TempDB --DB that everyone has where we can cause no harm
SET NOCOUNT ON --Supress the auto-display of rowcounts for appearance/speed
DECLARE @StartTime DATETIME --Timer to measure total duration
SET @StartTime = GETDATE() --Start the timer
--=============================================================================
-- Create and populate a Tally table
--=============================================================================
--===== Conditionally drop
IF OBJECT_ID('dbo.Tally') IS NOT NULL
DROP TABLE dbo.Tally
--===== Create and populate the Tally table on the fly
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
--===== Let the public use it
GRANT SELECT, REFERENCES ON dbo.Tally TO PUBLIC
--===== Simulate a passed parameter
DECLARE @Parameter VARCHAR(8000)
SET @Parameter = 'Johnson, Newyork'
--===== Create a table to store the results in
DECLARE @Elements TABLE
(
Number INT IDENTITY(1,1), --Order it appears in original string
Value VARCHAR(8000) --The string value of the element
)
--===== Decalre a variable to remember the position of the current comma
DECLARE @N INT
--===== Add start and end commas to the Parameter so we can handle
-- single elements
SET @Parameter = ','+@Parameter +','
--===== Preassign the current comma as the first character
SET @N = 1
--===== Loop through and find each comma, then insert the string value
-- found between the current comma and the next comma. @N is
-- the position of the current comma.
WHILE @N < LEN(@Parameter) --Don't include the last comma
BEGIN
--==== Do the insert using the value between the commas
INSERT INTO @Elements
VALUES (SUBSTRING(@Parameter,@N+1,CHARINDEX(',',@Parameter,@N+1)-@N-1))
--==== Find the next comma
SELECT @N = CHARINDEX(',',@Parameter,@N+1)
END
SELECT * FROM @Elements
May 25, 2009 at 12:07 pm
Hi SSC Rookie
You save my day!...
Great!
You gave me a solutions at the same time i learned from that:hehe:
Thank you so much;-)
May 25, 2009 at 12:33 pm
Hi
@Allister: You referenced Jeff's article, but pasted the RBAR split version 😉
@hunter: Please have a look to Jeff's article. The performance difference between a WHILE loop and a set-based (Tally table) version is really huge.
Greets
Flo
May 26, 2009 at 4:53 am
Arrrgh! totally missed that, Flo, thanks for that.
May 26, 2009 at 6:35 am
Allister Reid (5/26/2009)
Arrrgh! totally missed that, Flo, thanks for that.
No prob. You know I also make faults 😉
May 26, 2009 at 8:58 am
Wow yes you are right there is a huge difference between the two...Thanks to both of you....Thanks a lot i already revised my script:-D thanks a lot guys you are so great!
May 26, 2009 at 9:02 am
Hey Hunter, this site is fantastic, there are some real gems of articles (and folks) ...
May 26, 2009 at 9:08 am
Hi Rookie
Yup You Are right this site is so great and fantastic..it help the programmer a lot in analyzing and in some tricks on how to handle certain scenario using SQL...i really like this site
But this site will be not so useful without you guys...
Promise!
Two Thumbs Up for all of you...
😀
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply