I would like to retreive the last string from this pattern

,B110,A104,AUSL,
,L101,A101,01,

which means i want to retrieve AUSL and 01

thanks

You can do that playing with charindex,substring and reverse:

DECLARE @string varchar(200)

SET @string = ',B110,A104,AUSL,'
SELECT
REVERSE(SUBSTRING(REVERSE(@string),2,CHARINDEX(',' ,REVERSE(@string),2)-2))

SET @string = ',L101,A101,01,'
SELECT
REVERSE(SUBSTRING(REVERSE(@string),2,CHARINDEX(',' ,REVERSE(@string),2)-2))


Rubén Garrigós
Solid Quality Mentors

"tulip" wrote:

> I would like to retreive the last string from this pattern
>
> ,B110,A104,AUSL,
> ,L101,A101,01,
>
> which means i want to retrieve AUSL and 01
>
> thanks

You can use utility table with numbers to parse the delimited list:

CREATE TABLE Foo (
foo_key INT PRIMARY KEY,
foo_data VARCHAR(40));

INSERT INTO Foo VALUES(1, ',B110,A104,AUSL,');
INSERT INTO Foo VALUES(2, ',L101,A101,01,');

SELECT list_value
FROM (
SELECT SUBSTRING(foo_data, n, CHARINDEX(',', foo_data + ',', n) - n) AS
list_value,
n + 1 - LEN(REPLACE(LEFT(foo_data, n), ',', '')) AS list_idx
FROM (SELECT number
FROM master..spt_values
WHERE type = 'P'
AND number BETWEEN 1 AND 250) AS Nums(n)
CROSS JOIN Foo AS F
WHERE SUBSTRING(',' + foo_data, n, 1) = ','
AND n < LEN(foo_data) + 1) AS T
WHERE list_idx = 4;

--
Plamen Ratchev
http://www.SQLStudio.com

so how to retrieve the middle value, e.g. A104 and A101

thanks

"Rubén Garrigós" wrote:

> You can do that playing with charindex,substring and reverse:
>
> DECLARE @string varchar(200)
>
> SET @string = ',B110,A104,AUSL,'
> SELECT
> REVERSE(SUBSTRING(REVERSE(@string),2,CHARINDEX(',' ,REVERSE(@string),2)-2))
>
> SET @string = ',L101,A101,01,'
> SELECT
> REVERSE(SUBSTRING(REVERSE(@string),2,CHARINDEX(',' ,REVERSE(@string),2)-2))
>
>
> Rubén Garrigós
> Solid Quality Mentors
>
> "tulip" wrote:
>
> > I would like to retreive the last string from this pattern
> >
> > ,B110,A104,AUSL,
> > ,L101,A101,01,
> >
> > which means i want to retrieve AUSL and 01
> >
> > thanks

How about using a SPLIT() type of function? For example:



CREATE FUNCTION [dbo].[SplitStrings]
(
@List NVARCHAR(MAX),
@Delimiter CHAR(1) = ','
)
RETURNS @Items TABLE
(
Position INT IDENTITY(1,1) NOT NULL,
Item NVARCHAR(MAX) NOT NULL
)
AS
BEGIN
DECLARE
@Item NVARCHAR(MAX),
@Pos INT;

SET @List = @List + ' ';

WHILE DATALENGTH(@List) > 0
BEGIN
SET @Pos = CHARINDEX(@Delimiter,@List);

IF @Pos = 0
BEGIN
SET @Pos = DATALENGTH(@List)+1;
END

SET @Item = LTRIM(RTRIM(LEFT(@List, @Pos-1)));

INSERT @Items(Item) SELECT @Item;

SET @List = SUBSTRING(@List, @Pos+1, DATALENGTH(@List));
END
RETURN
END
GO

SELECT * FROM dbo.SplitStrings(',B110,A104,AUSL,', ',');
SELECT * FROM dbo.SplitStrings(',B110,A104,AUSL,', ',') WHERE Position = 3;
SELECT * FROM dbo.SplitStrings(',B110,A104,AUSL,', ',') WHERE Position = 4;

SELECT * FROM dbo.SplitStrings(',L101,A101,01,', ',');
SELECT * FROM dbo.SplitStrings(',L101,A101,01,', ',') WHERE Position = 3;
SELECT * FROM dbo.SplitStrings(',L101,A101,01,', ',') WHERE Position = 4;


You could also think about storing the data relationally instead of using
this ugly CSV string to encode different pieces of data inside a single
column... If you are going after individual parts of the string then storing
them separately makes a lot more sense than how you're currently doing it.


> so how to retrieve the middle value, e.g. A104 and A101
>
> thanks
>
> "Rubén Garrigós" wrote:
>
>> You can do that playing with charindex,substring and reverse:
>>
>> DECLARE @string varchar(200)
>>
>> SET @string = ',B110,A104,AUSL,'
>> SELECT
>> REVERSE(SUBSTRING(REVERSE(@string),2,CHARINDEX(',' ,REVERSE(@string),2)-2))
>>
>> SET @string = ',L101,A101,01,'
>> SELECT
>> REVERSE(SUBSTRING(REVERSE(@string),2,CHARINDEX(',' ,REVERSE(@string),2)-2))
>>
>>
>> Rubén Garrigós
>> Solid Quality Mentors
>>
>> "tulip" wrote:
>>
>>> I would like to retreive the last string from this pattern
>>>
>>> ,B110,A104,AUSL,
>>> ,L101,A101,01,
>>>
>>> which means i want to retrieve AUSL and 01
>>>
>>> thanks

what does this function suppose return with select statement?
i tried those select but didn't return me the values.

"Aaron Bertrand [SQL Server MVP]" wrote:

> How about using a SPLIT() type of function? For example:
>
>
>
> CREATE FUNCTION [dbo].[SplitStrings]
> (
> @List NVARCHAR(MAX),
> @Delimiter CHAR(1) = ','
> )
> RETURNS @Items TABLE
> (
> Position INT IDENTITY(1,1) NOT NULL,
> Item NVARCHAR(MAX) NOT NULL
> )
> AS
> BEGIN
> DECLARE
> @Item NVARCHAR(MAX),
> @Pos INT;
>
> SET @List = @List + ' ';
>
> WHILE DATALENGTH(@List) > 0
> BEGIN
> SET @Pos = CHARINDEX(@Delimiter,@List);
>
> IF @Pos = 0
> BEGIN
> SET @Pos = DATALENGTH(@List)+1;
> END
>
> SET @Item = LTRIM(RTRIM(LEFT(@List, @Pos-1)));
>
> INSERT @Items(Item) SELECT @Item;
>
> SET @List = SUBSTRING(@List, @Pos+1, DATALENGTH(@List));
> END
> RETURN
> END
> GO
>
> SELECT * FROM dbo.SplitStrings(',B110,A104,AUSL,', ',');
> SELECT * FROM dbo.SplitStrings(',B110,A104,AUSL,', ',') WHERE Position = 3;
> SELECT * FROM dbo.SplitStrings(',B110,A104,AUSL,', ',') WHERE Position = 4;
>
> SELECT * FROM dbo.SplitStrings(',L101,A101,01,', ',');
> SELECT * FROM dbo.SplitStrings(',L101,A101,01,', ',') WHERE Position = 3;
> SELECT * FROM dbo.SplitStrings(',L101,A101,01,', ',') WHERE Position = 4;
>
>
> You could also think about storing the data relationally instead of using
> this ugly CSV string to encode different pieces of data inside a single
> column... If you are going after individual parts of the string then storing
> them separately makes a lot more sense than how you're currently doing it.
>
>
> > so how to retrieve the middle value, e.g. A104 and A101
> >
> > thanks
> >
> > "Rubén Garrigós" wrote:
> >
> >> You can do that playing with charindex,substring and reverse:
> >>
> >> DECLARE @string varchar(200)
> >>
> >> SET @string = ',B110,A104,AUSL,'
> >> SELECT
> >> REVERSE(SUBSTRING(REVERSE(@string),2,CHARINDEX(',' ,REVERSE(@string),2)-2))
> >>
> >> SET @string = ',L101,A101,01,'
> >> SELECT
> >> REVERSE(SUBSTRING(REVERSE(@string),2,CHARINDEX(',' ,REVERSE(@string),2)-2))
> >>
> >>
> >> Rubén Garrigós
> >> Solid Quality Mentors
> >>
> >> "tulip" wrote:
> >>
> >>> I would like to retreive the last string from this pattern
> >>>
> >>> ,B110,A104,AUSL,
> >>> ,L101,A101,01,
> >>>
> >>> which means i want to retrieve AUSL and 01
> >>>
> >>> thanks
>
>

On Tue, 9 Sep 2008 08:16:01 -0700, tulip
<tulip@discussions.microsoft.com> wrote:

>what does this function suppose return with select statement?
>i tried those select but didn't return me the values.

I ran the code Aaron provided. The results I received:

Position Item
----------- -----------------------------------
1
2 B110
3 A104
4 AUSL
5

Position Item
----------- -----------------------------------
3 A104

Position Item
----------- -----------------------------------
4 AUSL

Position Item
----------- -----------------------------------
1
2 L101
3 A101
4 01
5

Position Item
----------- -----------------------------------
3 A101

Position Item
----------- -----------------------------------
4 01

Roy Harvey
Beacon Falls, CT

> i tried those select but didn't return me the values.

I don't understand, sorry. What exactly did you try, and what did you
receive? Did you create the function first?

so if the @list is a field value of another table, how to combine it with the
funtion below?
for example, select stringValue from StringTable, stringValue will be need
to parsed one by one to get the value of position 2, 3, 4.

thanks

"Aaron Bertrand [SQL Server MVP]" wrote:

> How about using a SPLIT() type of function? For example:
>
>
>
> CREATE FUNCTION [dbo].[SplitStrings]
> (
> @List NVARCHAR(MAX),
> @Delimiter CHAR(1) = ','
> )
> RETURNS @Items TABLE
> (
> Position INT IDENTITY(1,1) NOT NULL,
> Item NVARCHAR(MAX) NOT NULL
> )
> AS
> BEGIN
> DECLARE
> @Item NVARCHAR(MAX),
> @Pos INT;
>
> SET @List = @List + ' ';
>
> WHILE DATALENGTH(@List) > 0
> BEGIN
> SET @Pos = CHARINDEX(@Delimiter,@List);
>
> IF @Pos = 0
> BEGIN
> SET @Pos = DATALENGTH(@List)+1;
> END
>
> SET @Item = LTRIM(RTRIM(LEFT(@List, @Pos-1)));
>
> INSERT @Items(Item) SELECT @Item;
>
> SET @List = SUBSTRING(@List, @Pos+1, DATALENGTH(@List));
> END
> RETURN
> END
> GO
>
> SELECT * FROM dbo.SplitStrings(',B110,A104,AUSL,', ',');
> SELECT * FROM dbo.SplitStrings(',B110,A104,AUSL,', ',') WHERE Position = 3;
> SELECT * FROM dbo.SplitStrings(',B110,A104,AUSL,', ',') WHERE Position = 4;
>
> SELECT * FROM dbo.SplitStrings(',L101,A101,01,', ',');
> SELECT * FROM dbo.SplitStrings(',L101,A101,01,', ',') WHERE Position = 3;
> SELECT * FROM dbo.SplitStrings(',L101,A101,01,', ',') WHERE Position = 4;
>
>
> You could also think about storing the data relationally instead of using
> this ugly CSV string to encode different pieces of data inside a single
> column... If you are going after individual parts of the string then storing
> them separately makes a lot more sense than how you're currently doing it.
>
>
> > so how to retrieve the middle value, e.g. A104 and A101
> >
> > thanks
> >
> > "Rubén Garrigós" wrote:
> >
> >> You can do that playing with charindex,substring and reverse:
> >>
> >> DECLARE @string varchar(200)
> >>
> >> SET @string = ',B110,A104,AUSL,'
> >> SELECT
> >> REVERSE(SUBSTRING(REVERSE(@string),2,CHARINDEX(',' ,REVERSE(@string),2)-2))
> >>
> >> SET @string = ',L101,A101,01,'
> >> SELECT
> >> REVERSE(SUBSTRING(REVERSE(@string),2,CHARINDEX(',' ,REVERSE(@string),2)-2))
> >>
> >>
> >> Rubén Garrigós
> >> Solid Quality Mentors
> >>
> >> "tulip" wrote:
> >>
> >>> I would like to retreive the last string from this pattern
> >>>
> >>> ,B110,A104,AUSL,
> >>> ,L101,A101,01,
> >>>
> >>> which means i want to retrieve AUSL and 01
> >>>
> >>> thanks
>
>