SQL 2005 Express Select Row where column contains a value: SQL 2005 Express, Query

by: aneeshattingalPosted on 2009-10-23 at 14:56:40ID: 25649084

is it a text column or a varchar(max) column

by: 8080_DiverPosted on 2009-10-23 at 15:10:27ID: 25649171

I think this will work whether it is a TEXT or a VarChar column:

UPDATE yourtable
SET memo = REPLACE(memo, 'msgsrv', 'msgserver');

1:
2:

Select all Open in new window

by: BocefusPosted on 2009-10-23 at 15:30:58ID: 25649315

memo is a text variable. When I run your script I get the following error:

Msg 8116, Level 16, State 1, Line 2
Argument data type text is invalid for argument 1 of replace function.

by: BocefusPosted on 2009-10-23 at 15:32:02ID: 25649327

Also, I would like to list the rows before I go and replace them. I just want to be sure that it will work correctly first. Thanks

by: Ralph_AveryPosted on 2009-10-23 at 18:08:32ID: 25650221

Are you trying to locate data in a "text" field?

If so, select * from [table] where
memo like '%' + Cast(memo as varchar(max)) + '%'

by: Ralph_AveryPosted on 2009-10-23 at 18:11:18ID: 25650229

If you're happy with the results, you can do the

Update t set
memo = replace(cast(memo as varchar(max)), 'msgsvr', msgserver')
from [table]
-- the following lines are optional (they'll restrict it to where it exists...
where memo like '%' + Cast(memo as varchar(max)) + '%'

by: mazherPosted on 2009-10-23 at 22:07:58ID: 25650883

Replace Function can't be used becasue if length is greater than 8K then its wont works.

Below code handle all text or image data types

-- msgserver 
IF NOT OBJECT_ID('tempdb..#t1') IS NULL 
    DROP TABLE #t1 
IF NOT OBJECT_ID('tempdb..#tbChangeText') IS NULL 
    DROP TABLE #tbChangeText      
CREATE TABLE #t1 ( c1 int , Memo text )
--EXEC sp_tableoption 'tempdb..#t1', 'text in row', 'on'
INSERT  #t1
VALUES  (
          '1'
        , 'This msgsrv text.' ) 
INSERT  #t1
VALUES  ( '2' , 'This is text.' )
INSERT  #t1
VALUES  (
          '3'
        , 'This is text msgsrv.' )
INSERT  #t1
VALUES  ( '4' , 'This is text.' )
 
SELECT  ROW_NUMBER() OVER ( ORDER BY c1 ) RowNumber
      , *
INTO    #tbChangeText
FROM    [#t1] AS t
WHERE   PATINDEX('%msgsrv%' , memo) > 0
 
DECLARE @MaxID INT
  , @ICount INT 
DECLARE @ptrval varbinary(16)
DECLARE @InOff INT 
SELECT  @MaxID = MAX(RowNumber)
FROM    [#tbChangeText] AS tct
 
SET @ICount = 1 
ALTER DATABASE pubs SET RECOVERY SIMPLE ;
SELECT  *
FROM    [#t1] AS t
WHILE @ICount <= @MaxID
    BEGIN 
		
        SELECT  @ptrval = TEXTPTR(t.Memo)
              , @InOff = PATINDEX('%msgsrv%' , t.memo)
        FROM    [#t1] AS t
        INNER JOIN [#tbChangeText] AS tct
        ON      tct.[c1] = t.[c1]
        WHERE   tct.[RowNumber] = @ICount
        UPDATETEXT #t1.[Memo] @ptrval @InOff 5 'msgserver' ;
		
        SET @ICount = @ICount + 1
 
    end  
ALTER DATABASE pubs SET RECOVERY FULL ;
 
 
SELECT  *
FROM    [#t1] AS t

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:

Select all Open in new window

by: BocefusPosted on 2009-11-03 at 20:20:44ID: 25736384

I appreciate all of the responses, but either:

1. The offered scripts did not work; or gave errors; or

2. I was unsure of whether the script would change my data without me being able to verify the changes first.

I also was not sure of some of the syntax. My table name was 'tm9user.template' is this supposed to be entered with single quotes, double quotes or [ ] brackets?

where I see '%' in Ralph Avery's script is that correct or do I put the text value I'm looking for there ie 'msgsrv'?

the field is a text field. Thanks again

by: Ralph_AveryPosted on 2009-11-04 at 12:22:03ID: 25743512

I'm sorry. I gave you some bad code. Must have been sleepy.

The query to find the values would be....

select * from [table] where
Cast(memo as Varchar(Max)) like '%' + 'msgsrv' + '%'

You can even test to see what it will do with the results if you do... (NewValue will contain the value that the query will change the results to)

select * ,
--UPDATE t set
Memo = Replace(cast(memo as varchar(max)), 'msgsvr', 'msgserver')
From [table] t where
Cast(memo as Varchar(Max)) like '%' + 'msgsrv' + '%'

Then if you're satisfied with the results, you can remove the -- in front of the 'UPDATE' statement and run from that line down.

Note: mazher has a point about the problem that may result with the values being > 8000 characters. (4000 if it's unicode). You can add "And len(memo) > 7996 (since you're adding 3 characters to the overall length in the update.

If you're updating some critical data, I'd suggest copying the table and testing it on that first.

by: acperkinsPosted on 2009-11-04 at 17:16:17ID: 25745920

Ralph_Avery,

I suspect they are either using SQL Server 2000 or if they are using SQL Server 2005 the database has a compatibility level of 80, so CASTing to varchar(MAX) is not going to help much ...

by: BocefusPosted on 2009-11-09 at 14:27:23ID: 31645293

Thanks, it worked

Question

SQL 2005 Express Select Row where column contains a value

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

SQL 2005 Express

Query

MS SQL Server

SQL Server 2005

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Need individual assistance?

Our experts are ready to help.

Want to learn from the best?

Read articles from industry experts.

Working on a long term project?

Store your work and research.

What Makes Experts Exchange Unique?

Trusted by the world's most respected brands.

Faithfully serving IT professionals since 1996.

Related Solutions

Free Tech Articles

Cloud Class Webinars

Join the Community

Give a Little. Get a Lot.

Answers

3 Ways to Join

The Experts

The Experts

Testimonials

Testimonials

Testimonials

Business Clients

Business Clients

In the Press

In the Press

In the Press