Split a String? - 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

Other Forums

MS Access

Split a String?

Author

Topic

glpita
Starting Member

17 Posts

Posted - 2010-07-26 : 18:00:17

Hi all,

I have a column with 2 pieces of information consolidated into a single string. The 1st is a roof type (RT), while 2nd is a roof cover (RC). e.g.

GABLE-METAL
HIP-SHINGLE
MANSARD-METAL an so on.

Sometimes the RT might occur alone like:

GABLE
HIP
MANSARD

My issue is: How I can create two different columns, one with RT the other with RC?

Thanks a lot,

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-26 : 18:08:34

Here you go:


DECLARE @t table (c1 varchar(50))

INSERT INTO @t VALUES('GABLE-METAL')
INSERT INTO @t VALUES('HIP-SHINGLE')
INSERT INTO @t VALUES('MANSARD-METAL')
INSERT INTO @t VALUES('MANSARD')

SELECT
	RT =
		CASE 
			WHEN CHARINDEX('-', c1) <> 0 THEN LEFT(c1, CHARINDEX('-', c1)-1)
			ELSE c1
		END,
	RC = 
		CASE
			WHEN CHARINDEX('-', c1) <> 0 THEN SUBSTRING(c1, CHARINDEX('-', c1)+1, DATALENGTH(c1))
			ELSE ''
		END
FROM @t

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

Subscribe to my blog

glpita
Starting Member

17 Posts

Posted - 2010-07-26 : 18:14:16

Thanks a lot Tara. I'm not that expert in SQL. Actually I'm using the SQL inside MS Access. Is there any easier way to perform this? I have a basic query like SELECT, FROM, WHERE structure. Also, I have many more cases than the ones I posted. Can you suggest something in that structure? I mean, avoiding the Declare, Insert clauses?

Thanks again,

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-26 : 18:18:38

The DECLARE/INSERT is just to show you that it works. Change the SELECT query to your table name and column name, and you should be good.

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

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-26 : 18:19:25

To make it more clear what I mean, here you go:


SELECT
	RT =
		CASE 
			WHEN CHARINDEX('-', YourColumn) <> 0 THEN LEFT(YourColumn, CHARINDEX('-', YourColumn)-1)
			ELSE YourColumn
		END,
	RC = 
		CASE
			WHEN CHARINDEX('-', YourColumn) <> 0 THEN SUBSTRING(YourColumn, CHARINDEX('-', YourColumn)+1, DATALENGTH(YourColumn))
			ELSE ''
		END
FROM YourTable

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

Subscribe to my blog

glpita
Starting Member

17 Posts

Posted - 2010-07-26 : 19:47:55

It seems that CASE WHEN is not available in Access: I tried the following:

SELECT
RT =
IIF(CHARINDEX('-', RoofTyp) <> 0, LEFT(RoofTyp, CHARINDEX('-', RoofTyp)-1),RoofTyp),
RC = IIF(CHARINDEX('-', RoofTyp) <> 0, SUBSTRING(RoofTyp, CHARINDEX('-', RoofTyp)+1, DATALENGTH(RoofTyp)),'')
FROM 3_Results

But now says that CHARINDEX is not available in Access. Any ideas what's the equivalent statement?

Thanks a again

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-26 : 19:51:00

I'll move your topic to the Access forum.

I have zero experience with Access, so hopefully someone else can help.

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

Subscribe to my blog

glpita
Starting Member

17 Posts

Posted - 2010-07-26 : 19:51:54

OK Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources