Prev: Please help with Select Statement (Zip File)
Next: SQL Developer Edition Question
From: Stout on 22 Jul 2008 18:44
I have a ntext field in Sharepoint database that contains the results
of a choice field. There are 5 choices (multiselect allowed) and the
results of the choices are stored as ;#General;#Leasing;# ...etc

Is there a way to remove the ;# in query at run-time? I am trying to
remove those ;# or atleast the # when running a reporting services
report.

Not sure if that makes any sense.

Thanks.
From: Plamen Ratchev on 22 Jul 2008 19:47
Assuming the data in this column does not exceed 4000 characters, then you
can cast to NVARCHAR (on SQL Server 2005 use NVARCHAR(MAX))and then use
REPLACE:

CREATE TABLE Foo (
keycol INT PRIMARY KEY,
text_data NTEXT);

INSERT INTO Foo VALUES(1, ';#General;#Leasing');

SELECT keycol, REPLACE(CAST(text_data AS NVARCHAR(4000)), ';#', '')
FROM Foo;

HTH,

Plamen Ratchev
http://www.SQLStudio.com

 | 
Pages: 1
Prev: Please help with Select Statement (Zip File)
Next: SQL Developer Edition Question