How to Insert space in the text.

How to Insert space in the text.

Post by Divye » Sun, 17 Aug 2003 01:20:10



Hi there,

In SQL 7, I have a table with field called Narrative.

It contains data as following:

~Acme Industries
XYZ Corp
~~Beck Co.
~~~Union Inc.
~~Tribeca Bros.
~~~~~INCA Inc.
Yem Inc.

What I want to do is put a space after the last tilde (~) in records
that contain tildes.

The result should look like

~ Acme Industries
XYZ Corp
~~ Beck Co.
~~~ Union Inc.
~~ Tribeca Bros.
~~~~~ INCA Inc.
Yem Inc.

Any help is appreciated.

Divyesh

 
 
 

How to Insert space in the text.

Post by Vishal Parka » Sun, 17 Aug 2003 01:35:46


Try;

create table #x(ind varchar(500))
insert into #x select '~Acme Industries' union all
select 'XYZ Corp' union all
select '~~Beck Co.' union all
select '~~~Union Inc.' union all
select '~~Tribeca Bros.' union all
select '~~~~~INCA Inc.'

--Required query:

update #x set ind =  left (ind, patindex('%[^~]%', ind) - 1) + ' ' +
right(ind, (len(ind) - patindex('%[^~]%', ind) + 1 ))
where ind like '~%'

select * from #x

--
-Vishal

Quote:> Hi there,

> In SQL 7, I have a table with field called Narrative.

> It contains data as following:

> ~Acme Industries
> XYZ Corp
> ~~Beck Co.
> ~~~Union Inc.
> ~~Tribeca Bros.
> ~~~~~INCA Inc.
> Yem Inc.

> What I want to do is put a space after the last tilde (~) in records
> that contain tildes.

> The result should look like

> ~ Acme Industries
> XYZ Corp
> ~~ Beck Co.
> ~~~ Union Inc.
> ~~ Tribeca Bros.
> ~~~~~ INCA Inc.
> Yem Inc.

> Any help is appreciated.

> Divyesh


 
 
 

1. TEXT field in Blob Space vs Table Space

Hello all,
    I'm creating a table that has a TEXT field. When asked where the
blobspace should reside I choose the table space. Any query on this
table is extreamly slow (about 800,000 records). I was wondering if the
table would have been quicker if I would have stored that field in a
Blob Space. I had a blobspace created on our filesystem and was trying
to do a speed test between the two choices but I got a -603 error when
using the TEXT field that uses the blob space and I figured I would just
ask before I go through all the trouble of seeing what is wrong with our
blob space if it is not needed.

Thanks,
Richard Krenek

2. ODBC and SQL-views

3. Inserting an empty string inserts a space

4. Newbie question on OLE

5. How to insert text into an existing text field

6. Problem using ADO SHAPE

7. BULK INSERT of Fixed Width Text - With Missing Data in Text File

8. Changing Datatypes

9. Maximum size of text inserted into a table of type text,ntext

10. Inserting some text after each row in a text file

11. Pbm when I insert text + text

12. Pbm when I insert Text + Text