Splitting a single field into two based on upper character occurrence

[nemmeh]

SQL Syntax help needed (splitting a single field)
Hey everyone,

Need a little help with creating a query to split some data. If there was a common character associated with this field then using the substring and trim functions would probably allow me to do this, however my sql is a little rusty when it comes to this nature of things.

I've basically got a single field that contains a list of names, the data should really be split into two separate fields called FirstName and LastName. At the moment, the data is at least consistent in the overall scheme that the FirstName and LastName start with capitals.

Example:

JohnDoe
JaneDoe
RayWoodie
ToddEmrich

etc....
As you can see the consistency is there that I believe I should be able to just construct a query using some type of functions to just set the first upper letter then the lower string immediately following until another upper is hit. As far as actually constructing this query is where I get lost as I'm not familiar with the functions that I would need to use to grab just that segment of data.

Can anyone offer some advice of which functions and/or possibly throw a quick query together to split the data for me?

Btw, the database that i'm working with is a borland (paradox) type so some of the more common t-sql functions may not be supported.

Thanks,

Nem

 

[amrita418]

I am positive, there is a better method to do this but if your data set is small then the performance should not be a problem.

CREATE TABLE #TestNemmeh (NAME VARCHAR(20))

INSERT INTO #TestNemmeh VALUES('JohnDoe')
INSERT INTO #TestNemmeh VALUES('JaneDoe')
INSERT INTO #TestNemmeh VALUES('RayWoodie')
INSERT INTO #TestNemmeh VALUES('ToddEmrich')

DECLARE 
@v_counter 	INT,
@v_string 	VARCHAR(20),
@v_name 	VARCHAR(20),
@v_test 	BIT,
@v_firstname 	VARCHAR(20),
@v_lastname 	VARCHAR(20)

DECLARE C1 CURSOR FOR SELECT 	NAME 
		      FROM 	#TestNemmeh

SET 	@v_counter = 1
SET 	@v_name = ''
SET 	@v_test = 0
SET 	@v_firstname = ''
SET 	@v_lastname = ''

OPEN 	C1
FETCH   NEXT FROM C1 INTO @v_name

WHILE 	@@FETCH_STATUS = 0
BEGIN
	WHILE @v_test = 0 
	BEGIN
		IF (ASCII(SUBSTRING(@v_name, @v_counter + 1, 1)) between 65 and 91)
		BEGIN
			SET 	@v_firstname = SUBSTRING(@v_name, 1, @v_counter)
			SET 	@v_lastname = SUBSTRING(@v_name, @v_counter + 1, len(@v_name))
			PRINT 	'First Name: ' + @v_firstname + '   Last Name: ' + @v_lastname
			SET 	@v_test = 1
		END
		ELSE 
		BEGIN
			SET 	@v_counter = @v_counter + 1
		END
	END

	SET 	@v_counter = 1
	SET 	@v_name = ''
	SET 	@v_test = 0
	SET 	@v_firstname = ''
	SET 	@v_lastname = ''
	FETCH 	NEXT FROM C1 INTO @v_name
END
CLOSE 	C1
DEALLOCATE C1

Also, I am not familier with paradox so am not sure of the functions that it supports.

Regards,
AA

 

[earthandfire]

There is a Paradox forum (its called Corel: Paradox) forum177, someone there will probably be able to assist you.


Hope this helps.

 

[nemmeh]

Yes, it is a small table with only about 3000 records. Unfortunately though those functions are not supported.

Thanks for the query though, I'll try to seek some guidance from the paradox forum.

Thanks again.

Nem

 

[micha123]

So just for fun....

CREATE FUNCTION fn_FindCapital (@p_chr varchar(1000))
RETURNS smallint
AS
BEGIN
   DECLARE @pos  smallint
   SET @pos = 2   --NOT TO CHECK FIRST CHAR
   while ascii(substring(@chrLeft,@pos,1)) NOT between 65 and 90
         SET @pos = @pos + 1

   return @pos
END
GO

SELECT 
  SUBSTRING(NameFld,1,dbo.fn_FindCapital(NameFld)-1) AS FirstName, 
  SUBSTRING(NameFld,dbo.fn_FindCapital(NameFld),Length(NameFld)) AS LastName
FROM MyTable
GO

"A long life is when each day is full and every hour wide!"
(A Jewish Mentor)
M.