How to query for sub-string of records? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

How to query for sub-string of records?

Author

Topic

omoo
Starting Member

5 Posts

Posted - 2005-11-24 : 06:40:49

Hi,

I have the follow records:
N1235-A123-U876
M2345-R434-T365
N1235-A168-U824
M3457-Z541-F234
M4321-R354-U543

I want to have the following distinct output:
N1235-A
M2345-R
M3457-Z
M4321-R
The output is the 1st 5 characrters and "-" and on more character after that. May I know how I can do this?

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2005-11-24 : 06:51:09

select distinct left(field_name,7) from Tablename

regards
Sachin

Don't sit back because of failure. It will come back to check if you still available. -- Binu

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-24 : 06:53:37

If the length is fixed then


Select distinct data from 
	(
	select left(col,7) as data from yourTable
	) T

Madhivanan

Failing to plan is Planning to fail

omoo
Starting Member

5 Posts

Posted - 2005-11-24 : 07:00:06

Hi, thanks for the suggestion. I got an error which says "Left:invalid identifier"

I am using SQL 8.1

quote:
Originally posted by sachinsamuel

select distinct left(field_name,7) from Tablename

regards
Sachin

Don't sit back because of failure. It will come back to check if you still available. -- Binu

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-24 : 07:01:12

Are you using SQL Server?

Madhivanan

Failing to plan is Planning to fail

omoo
Starting Member

5 Posts

Posted - 2005-11-24 : 07:03:33

I'm sorry. It's SQL 5.1

quote:
Originally posted by omoo

Hi, thanks for the suggestion. I got an error which says "Left:invalid identifier"

I am using SQL 8.1

omoo
Starting Member

5 Posts

Posted - 2005-11-24 : 07:04:44

Hmm... I am not sure but the program I am using is called "PL/SQL Developer"

quote:
Originally posted by madhivanan

Are you using SQL Server?

Madhivanan

Failing to plan is Planning to fail

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2005-11-24 : 07:15:35

Left will work fine with SQL server. I am not aware of SQL 8.1 or SQL 5.1

Don't sit back because of failure. It will come back to check if you still available. -- Binu

omoo
Starting Member

5 Posts

Posted - 2005-11-24 : 07:21:47

Hi,

I have got it. I use SUBSTR(column_name, 1, 7)

Thanks for the help!

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-11-24 : 07:27:22

"PL/SQL Developer"...ORACLE????...This site deals 99.9% of the time with MSSQL
dbforums.com might be a better long term bet for you.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-24 : 07:40:47

This is SQL Server Forum. If you are using other better to post at Relevent Forums

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources