October 23, 2007 at 11:17 pm
Bob Bridges (10/23/2007)
I've got a problem that I can't figure out... I'm using the following as suggested to get the data to the right of the delimiter...(SELECT STUFF(GL_SWT,1,CHARINDEX('.', @col+'.'),'')
It works fine when there are 5 characters to the left of the delimiter but when I have 6 characters to the left of the delimiter, the result set includes the delimiter.
Any ideas?
Thanks,
Bob
Bob, you need to post all the code, I think... you have an extra parenthesis in your code above and idicates that it's part of some other code. When I try this... the code works just fine....
[font="Courier New"]DECLARE @Col VARCHAR(35)
SET @Col = '12345.67890'
SELECT STUFF ( @col, 1, CHARINDEX('.', @Col+'.'), '' )
SET @Col = '123456.7890'
SELECT STUFF ( @col, 1, CHARINDEX('.', @Col+'.'), '' )[/font]
--Jeff Moden
Change is inevitable... Change for the better is not.
October 24, 2007 at 6:39 am
Jeff,
You got me on the right track... the problem is in the "SET @col" statement. I had
SET @col = 'Part2.Part1'
What I didn't realize is that the "Part2" and "Part1" are actually placeholders (literal number of characters). When I made it 123456.12345 it put 6 digits before the delimiter instead of 5 and that fixed it.
Thanks again, Jeff!
Bob
October 24, 2007 at 7:27 am
Just a concept piece I was playing with. Not good for very large data sets in the current form but does a good job as a simple tool
Inputs are
@info-2 - which is the data you want back, use a number for a specific position, use 'MAX' or 'MAXIMUM' to get the last position, use 'MIN' or 'MINIMUM' to get the first position (same as using 1), use 'CNT' or 'COUNT' to get the number of positions, use 'EMPTY' to get the count of all positions with no data (such as PART1...PART4 you get a count of 2), and use 'CNTDEL' to get the number of occurrances of the delimiter.
@delimiter - the delimiter you want to use
@input - the string you want to parse.
I am sure there are some enhancements that can be made but this is as far as I went for the time being
----------------------------Begin Code---------------------------
CREATE FUNCTION dbo.GetInfo(@info varchar(7), @delimiter as varchar(5), @input varchar(8000))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @tbl TABLE (IDX tinyint NOT NULL IDENTITY(1,1), VAL varchar(35) NOT NULL)
DECLARE @output varchar(8000)
WHILE CHARINDEX(@delimiter,@input) > 0
BEGIN
INSERT @tbl (VAL) VALUES (LEFT(@input,CHARINDEX(@delimiter,@input) - 1))
SET @input = RIGHT(@input, LEN(@input) - CHARINDEX(@delimiter,@input))
END
INSERT @tbl (VAL) VALUES (@input)
IF ISNUMERIC(@info) = 1
SET @output = (SELECT VAL FROM @tbl WHERE IDX = CAST(@info as tinyint))
ELSE
BEGIN
IF @info-2 = 'MAX' OR @info-2 = 'MAXIMUM'
SET @output = (SELECT VAL FROM @tbl WHERE IDX = (SELECT MAX(IDX) I FROM @tbl))
IF @info-2 = 'MIN' OR @info-2 = 'MINIMUM'
SET @output = (SELECT VAL FROM @tbl WHERE IDX = (SELECT MIN(IDX) I FROM @tbl))
IF @info-2 = 'CNT' OR @info-2 = 'COUNT'
SET @output = (SELECT COUNT(VAL) FROM @tbl)
IF @info-2 = 'EMPTY'
SET @output = (SELECT COUNT(VAL) FROM @tbl WHERE LEN(VAL) = 0)
IF @info-2 = 'CNTDEL' -- Count number of delimiter occurances.
SET @output = (SELECT COUNT(VAL) - 1 FROM @tbl)
END
RETURN(@output)
END
GO
October 26, 2007 at 4:55 pm
Sometimes we get data from all kinds of sources that often needs to be converted to a more usable form.
Parsing functions should be a dime a dozen. I would be surprised if any T-SQL text didn't have at least one to illustrate string functions. However, here is a general purpose one. It will be overkill if you know you will be working with strings that always have only one delimiter (as you show in your example) but you may want something like this for more general use.
This can be easily modified to a stored proc that returns all the tokens broken up into a result set if that's more what you need.
/*
Author:
Tomm Carr
Date:
10/26/2007
Description:
Returns the n'th token found in a string. A token is the
substring that lies between delimiters. For example, with
a string '111.222.333.444.555.666.777.888.999' and the
delimiter '.' (period), the tokens are '111', '222', etc.
The tokens are numbered starting at 1 -- token 1 is '111',
token 2 is '222', etc.
NULL is returned if the n'th token was not found (n <= 0 or
specifies the 10th token in a 9-token string) or if either
the pattern string or the delimiter character is NULL.
Examples:
select dbo.GetToken( '111.222.333.444.555.666.777.888.999', '.', 1 ) -- should return 111
select dbo.GetToken( '111.222.333.444.555.666.777.888.999', '.', 4 ) -- should return 444
select dbo.GetToken( '111.222.333.444.555.666.777.888.999', '.', 9 ) -- should return 999
select dbo.GetToken( '111.222.333.444.555.666.777.888.999', '.', 10 ) -- should return NULL
select dbo.GetToken( '111.222.333.444.555.666.777.888.999', 'X', 1 ) -- should return the
entire string.
*/
create function GetToken (
@Pattern varchar(8000),
@Delim char(1),
@Ind int
)
returns varchar(8000)
as
begin
declare @Token varchar(8000),
@Pos int,
@Pos1 int,
@Index int;
if @Ind > 0
begin
select @Index = 1,
@Pos1 = 0;
set @Pos = CharIndex( @Delim, @Pattern );
while @Pos > 0 and @Index < @Ind
begin
set @Pos1 = @Pos + 1;
select @Pos = CharIndex( @Delim, @Pattern, @Pos1 ),
@Index = @Index + 1;
end--while
if @Pos = 0 and @Index = @Ind
set @Pos = Len( @Pattern ) + 1;
if @Pos > 0
set @Token = SubString( @Pattern, @Pos1, @Pos - @Pos1 );
end--if
return @Token;
end--function
I put this through only a few cursory tests so if you find any flaws, let me know.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
October 26, 2007 at 5:25 pm
Bob Bridges (10/24/2007)
Jeff,You got me on the right track... the problem is in the "SET @col" statement. I had
SET @col = 'Part2.Part1'
What I didn't realize is that the "Part2" and "Part1" are actually placeholders (literal number of characters). When I made it 123456.12345 it put 6 digits before the delimiter instead of 5 and that fixed it.
Thanks again, Jeff!
Bob
My pleasure, Bob... thank you for the feedback.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply