Who's On?

	Membership:
	Latest: Saef
	Past 24 Hours: 0
	Prev. 24 Hours: 0
	Overall: 5226

	People Online:
	Visitors: 384
	Members: 0
	Total: 384

Name	Points
Greg Moeller	4184
David Williams	3349
JonA	3291
Kat V	2984
Woozy	1973
Jimmy Chiu	1883
Kwane McNeal	1437
Ragu Raghavan	1372
Roger French	1315
mark.cook	1244

Query to split string into two parts

4 Replies

0 Subscribed to this topic

22 Subscribed to this forum

Sort:

Author

Messages

DavidBrianCampbell

Basic Member

Posts: 13

9/19/2007 4:44 PM

I am trying to split a number into two parts and wondering if anyone has a suggested syntax? (e.g. 107511 split into 107 and 511 and then alias each with the "AS" keyword.) Thanks!

Chris Martin posted this when I was trying two join 3 fields into one (I'm kinda doing the reverse now):

trim(last_name) || ', ' || trim(first_name) || ' ' || case when trim(middle_init) is null then trim(middle_init) else trim(middle_init) || '.' end

DavidBrianCampbell

Basic Member

Posts: 13

9/19/2007 5:16 PM

To be more specific, the field that I'm trying to split is EMPLOYEE.DEPARTMENT (String [5])
So for example I want this value: "10050" split into "100" and "50" and

SELECT EMPLOYEE.EMPLOYEE, EMPLOYEE.DEPARTMENT, rtrim(DEPARTMENT-' ') AS LAST2 ltrim(DEPARTMENT-' ') AS FIRST3 FROM EMPLOYEE

Thanks!

Chris Martin

Veteran Member

Posts: 277

9/19/2007 5:25 PM

Use the substr function:

a) substr(department,1,3) as FIRST3
b) substr(department,4,2) as LAST2

The LAST2 will only give you what you want if all of your departments 5 characters long.

k-rock

Veteran Member

Posts: 142

9/20/2007 11:42 AM

I'm not a Crystal user, but there has to be some function like LEN in excel.

substr(department, 1, 3) as FIRST3
substr(department, 4, LEN(department)-3) as LAST

that should give you the remainder of the string

John Henley

Posts: 3353

9/20/2007 2:39 PM

Brian, are you trying to do this within the SQL command itself (i.e. you're not reporting against tables but against a SQL command [or a SQL Expression in Crystal, which uses the vendor-specific SQL syntax] (and if so, what flavor: Oracle, MSSQL, etc.?), or via a Crystal Formula? The reason I ask is because the syntax is different.