February 6, 2012 at 4:17 am
Hi,
I need to use the split function to display values separated by comma(,). However, the problem I am facing is that the values itself are separated by space. For e.g. I need to use the split function for the following values, which are separated by commas and also have a space in between them:
KNL RTM, CHGLS BSL, KPT OPO, KBE ANR
I am using this syntax for the split function:
MG_BOOKING.BOOKING_OFFICE_CD IN (SELECT DISTINCT Item FROM dbo.Split(@Booking_Office,',')).
The split function is as below:
------------------------------------------------------
ALTER FUNCTION [dbo].[Split]
(
@ItemList NVARCHAR(4000),
@delimiter CHAR(1)
)
RETURNS @IDTable TABLE (Item VARCHAR(50))
AS
BEGIN
DECLARE @tempItemList NVARCHAR(4000)
SET @tempItemList = @ItemList
DECLARE @i INT
DECLARE @Item NVARCHAR(4000)
SET @tempItemList = REPLACE (@tempItemList, ' ', '')
SET @i = CHARINDEX(@delimiter, @tempItemList)
WHILE (LEN(@tempItemList) > 0)
BEGIN
IF @i = 0
SET @Item = @tempItemList
ELSE
SET @Item = LEFT(@tempItemList, @i - 1)
INSERT INTO @IDTable(Item) VALUES(@Item)
IF @i = 0
SET @tempItemList = ''
ELSE
SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)
SET @i = CHARINDEX(@delimiter, @tempItemList)
END
RETURN
END
-----------------------------------------------------
February 6, 2012 at 6:27 am
I suggest that you test Jeff Moden's 8K splitter.
February 6, 2012 at 6:40 am
pwalter83 (2/6/2012)
Hi,I need to use the split function to display values separated by comma(,). However, the problem I am facing is that the values itself are separated by space. For e.g. I need to use the split function for the following values, which are separated by commas and also have a space in between them:
KNL RTM, CHGLS BSL, KPT OPO, KBE ANR ...
It's not clear what you want to do - what values do you want to extract from this string?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 6, 2012 at 6:58 am
ChrisM@home (2/6/2012)
pwalter83 (2/6/2012)
Hi,I need to use the split function to display values separated by comma(,). However, the problem I am facing is that the values itself are separated by space. For e.g. I need to use the split function for the following values, which are separated by commas and also have a space in between them:
KNL RTM, CHGLS BSL, KPT OPO, KBE ANR ...
It's not clear what you want to do - what values do you want to extract from this string?
Theses values are stored in a variable for a parameter and I want to display ther matching values in the report. for eg. if the user selects 'KNL RTM', then all matching data should be displayed. The problem is that there is a space between the values due to which the comparing is not possible and hence no data is displayed on the report.
I have created a split function which is useful but it only works if no spaces exist between the values.
February 6, 2012 at 7:05 am
pwalter83 (2/6/2012)
ChrisM@home (2/6/2012)
pwalter83 (2/6/2012)
Hi,I need to use the split function to display values separated by comma(,). However, the problem I am facing is that the values itself are separated by space. For e.g. I need to use the split function for the following values, which are separated by commas and also have a space in between them:
KNL RTM, CHGLS BSL, KPT OPO, KBE ANR ...
It's not clear what you want to do - what values do you want to extract from this string?
Theses values are stored in a variable for a parameter and I want to display ther matching values in the report. for eg. if the user selects 'KNL RTM', then all matching data should be displayed. The problem is that there is a space between the values due to which the comparing is not possible and hence no data is displayed on the report.
I have created a split function which is useful but it only works if no spaces exist between the values.
Your split function is faulty. Rather than fix it, why not follow Ron's suggestion and use Jeff Moden's splitter instead?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 6, 2012 at 7:11 am
Once you have eliminated the commas then use
DECLARE @Res VARCHAR(10)
SET @Res = 'KNL RTM'
SELECT REPLACE(@Res,' ','')
Result:
KNLRTM
February 6, 2012 at 8:25 am
bitbucket-25253 (2/6/2012)
Once you have eliminated the commas then use
DECLARE @Res VARCHAR(10)SET @Res = 'KNL RTM'
SELECT REPLACE(@Res,' ','')
Result:
KNLRTM
Thanks Ron,
But the thing is I dont wish to join the values, instead I want to find the matching values(with the space included) in the database and then display them with the spaces intact.
February 6, 2012 at 8:38 am
pwalter83 (2/6/2012)
bitbucket-25253 (2/6/2012)
Once you have eliminated the commas then use
DECLARE @Res VARCHAR(10)SET @Res = 'KNL RTM'
SELECT REPLACE(@Res,' ','')
Result:
KNLRTM
Thanks Ron,
But the thing is I dont wish to join the values, instead I want to find the matching values(with the space included) in the database and then display them with the spaces intact.
Then forget using the REPLACE function and just use Jeff Moden's 8K splitter. If that give you a problem please repost and I am sure some one will give you further assistance.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply