Hello vprax-ga!
I have taken the liberty of answering your question by way of a full
SQL script, which is reproduced below. This was created on MS SQL
Server 2000, and should work on any standard installation of MS SQL
Server 2K. I believe it should also work on SQL Server 7, however I
did not test it in that environment.
The script below performs several operations:
1. Creates a table (CaretStringTable) to contain some data used in the
example.
2. Creates a user-defined function (ParseCaretString), used to process
the strings.
3. Adds several rows of example data to the table.
4. Does a full SELECT * against the table, displaying all rows and the
data in them, so you can compare it against the results desired.
5. Performs a single-statement query which parses the strings within
the SQL statement. As you can see from the comments inline, this is
provided for informational purposes primarily, because it's not a very
efficient mechanism, as it requires the server to do more than it
needs to. However, it does work.
6. Performs the same operation using the ParseCaretString function,
resulting in the same resultset. This is the preferred method, as it
results in a much cleaner set of T-SQL code, and allows for reuse of
the function in other statements.
There are some caveats to this example. First, the function I wrote,
and the SQL code I created for this example may not work properly if
there are fewer than 2 carets (or zero carets) in the string being
parsed. In such cases, spurious results may be returned. You can test
for this by modifying the function body to return the appropriate
results if the string is not formatted as the function expects.
For more information on these functions and how they are put together,
I would recommend searching the Books Online for "string functions" or
"string manipulation functions". For more information on how to create
and use user-defined functions, simply search for "User-Defined
Functions" in the Books online.
Notes:
There is no "TRIM" function in SQL server, but there is a LTRIM and
RTRIM function, which can be combined as shown to remove both leading
and trailing spaces from a string. The "REVERSE" function is used with
the "LEFT" function to locate the right-most instance of a "^"
character. The same technique is used, with the "RIGHT" function, to
remove the characters preceding the third "^" character.
The "^" character is a wildcard for certain string functions, namely
the "not" expression. However, it only takes on this characteristic if
enclosed in [] characters. Search Books Online for "wildcards" for
more information on this.
Functions used and a brief description:
RIGHT(expression, numchars): Returns the number of characters
specified in numchars from the right-hand side of a string.
LEFT(expression, numchars): Returns the number of characters specified
in numchars from the left of a string.
REVERSE(expression): Returns the reverse of a string- "the" becomes
"eht". Used in this case to quickly obtain the fourth (last) caret
from a string.
LEN(expression): Returns the number of characters in a string.
LTRIM, RTRIM: Trims leading and trailing whitespace from a string,
respectively.
Beneath the line below you will find a SQL script. Copy and paste it
into Query Analyzer, connected to either the Northwind database, or
any database you are authorized to create objects in, and run the
query. It will return several results to you. It may be necessary to
reformat the script text if Google Answers wraps it inappropriately.
I sincerely hope that this information is of use to you.
Regards,
Cyclometh (cyclometh-ga)
--------------------------------------------
--First, create a table to hold the values we'll be testing.
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[CaretStringTable]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[CaretStringTable]
GO
CREATE TABLE [dbo].[CaretStringTable] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[STRINGTOPARSE] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY]
GO
--Next, create a function to parse the strings.
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[ParseCaretString]') and xtype in (N'FN', N'IF',
N'TF'))
drop function [dbo].[ParseCaretString]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION ParseCaretString(@parsestring AS NVARCHAR(200))
RETURNS VARCHAR(200)
AS
BEGIN
--A temporary string variable to hold the string
--as it is being manipulated.
declare @tmpstr varchar(200)
SET @tmpstr=left(@parsestring, len(@parsestring)-charindex('^',
reverse(@parsestring)))
RETURN ltrim(rtrim(right(@tmpstr, charindex('^',
reverse(@tmpstr))-1)))
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--Insert some test values into the table.
INSERT INTO CaretStringTable
(STRINGTOPARSE)
VALUES
('a ^test ^^ this is the text ^')
INSERT INTO CaretStringTable
(STRINGTOPARSE)
VALUES
('this ^ is ^ some ^ other text. ^ some trailing text to be
ignored.')
INSERT INTO CaretStringTable
(STRINGTOPARSE)
VALUES
('and a ^ third string ^^ this text will be displayed.^ this text will
not.')
--Do a full select against the test table, showing all the text
including carets.
select * from caretstringtable
--Directly select the values in question, in one statement.
--This is NOT a very efficient mechanism, but does work. It has to do
a lot of string
--manipulation, in particular performing the LEFT() operation twice,
because we aren't
--using a variable to store the result. This is provided for
educational purposes,
--to show that you can do this in one SQL statement. However, this is
extremely
--inefficient.
select LTRIM(RTRIM(right(left(STRINGTOPARSE,
len(STRINGTOPARSE)-charindex('^', reverse(STRINGTOPARSE))),
charindex('^', reverse(left(STRINGTOPARSE,
len(STRINGTOPARSE)-charindex('^', reverse(STRINGTOPARSE))-1))))))
FROM caretstringtable
--Do the same as above, except call the function we declared above.
--Using a custom function is much more efficient than doing the
operation
--above, as the string manipulation functions must only be called
once. This
--also results in a much cleaner SQL statement, as you can see.
SELECT dbo.ParseCaretString(STRINGTOPARSE) from caretstringtable |
