Quick Question - 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 2005 Forums

Transact-SQL (2005)

Quick Question

Author

Topic

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-08-20 : 18:20:08

Hi Guys

I have a table named Orders, which has a field called OrderRef. Sample data looks like:
O112298/01
O112299/01
O112299/02
O112299/03
0112300/01
0112301/01
0112301/02

Everthing before the '/' is the order number, and everything after is the order line. IS there a way to show these two items seperatley, for example:
OrderNumber,OrderLine
O112298,01
O112299,01
O112299,02
O112299,03
0112300,01
0112301,01
0112301,02

Thanking you in advance!!!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-20 : 18:43:01

SELECT LEFT(Column1, 7) AS OrderNumber, RIGHT(Column1, 2) AS OrderLine

If the sizes change, you can use CHARINDEX to locate "/" and then use LEFT/SUBSTRING.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-08-21 : 02:55:12

Hi

Thanks for that!

How would you use the CHARINDEX as I have a feeling the lengths will change soon.

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-21 : 03:33:28

select substring(col,1,charindex('/',col)-1),substring(col,charindex('/',col)+1,len(col)) from table

Read about that function in sql server help file

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-21 : 04:17:57

or use parsename

SELECT PARSENAME(REPLACE(OrderRef,'/','.'),2) AS OrderNumber,PARSENAME(REPLACE(OrderRef,'/','.'),1) AS OrderLine FROM Orders

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources