Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Query

Author

Topic

vicki
Posting Yak Master

117 Posts

Posted - 2002-02-07 : 15:51:30

I have the data in the table look like this: BOS DEC109, I want to run the query that return back to me everything before DEC109,
Here what I try
select Field1
from Dibind
where right( Field1, 6 like 'DEC109')

but it wasn't work and just return me with BOS DEC109

Help please

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2002-02-07 : 16:03:50

try like '%DEC109'

BTW, if you want the returned set changed then you have to trim the value in the select.

for example:

select field from table where field like '%DEC109'

could return

djhfdkjDEC109

but if you wanted it to return DEC109 only then you would have to trim the result set thusly:

Select right(field,6) from table where field like '%DEC109'

HTH

Edited by - cat_jesus on 02/07/2002 16:07:04

chadmat
The Chadinator

1974 Posts

Posted - 2002-02-07 : 16:09:12

select left(field1, (charindex('DEC109', field1)-1))

HTH
-Chad

vicki
Posting Yak Master

117 Posts

Posted - 2002-02-07 : 16:11:01

I alredy did but it still not work either

chadmat
The Chadinator

1974 Posts

Posted - 2002-02-07 : 16:22:11

This does work, I tested it.

Pleas re-explain what you want.

I understand:
"...I want to run the query that return back to me everything before DEC109..."

to mean you would like BOS to be returned (Everything before)

If you run
select left('BOS DEC109', (charindex('DEC109', 'BOS DEC109')-1))

it returns BOS .

Just replace 'BOS DEC109' with your column name, and add a from clause and you should be set

-Chad

vicki
Posting Yak Master

117 Posts

Posted - 2002-02-07 : 16:23:20

I ran your command but I got the error said "Undefined Function charindex in Express

eddie
Starting Member

45 Posts

Posted - 2002-02-07 : 16:25:06

If you always know the end of the substring will be six characters..this should work..

select left(value1,(len(value1)-6))
from tablename where value1 like '%dec109'

Eddie

chadmat
The Chadinator

1974 Posts

Posted - 2002-02-07 : 16:47:36

What version of SQL Server?

I know it was in 7.0, I thought it was in 6.5 too, but I'm not sure about that, and don't have 6.5 BOL to reference.

-Chad

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2002-02-07 : 17:22:12

you could do this

select replace(field,'DEC109','') from table where field like '%DEC109'

Cat

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources