SQL - split a result, add a character then rejoin 1

[robfromuk]

Hi folks, I have a complex query which I need to split, add a character in the middle, then rejoin.

Here is an example:

SELECT
DISTINCT ROB.CLIENTNO
FROM ROB, CLIENTINFO CL, ITFOLDERS IT, XWALK X
WHERE (ROB.CLIENTNO NOT LIKE '6%') AND (ROB.CLIENTNO LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9]')
AND ROB.ROB_ID = CL.PARENTNAME
AND CL.CNTRLNO = IT.CNTRLNO
AND X.XWALK = ROB.CLIENTNO

This is working correctly. It uses data in the format xxx-xxxxx from the ROB table and compares it with equivalent data in the XWALK table, displaying the distinct CLIENTNO's.

Here is my issue:

I also have data in the format xxxxxxxx.
In this instance, I need to insert a character after the third x, so it is xxx-xxxxx and can be looked up as per the above statement.

I therefore need to:
1) Find all xxxxxxxx.
2) Split it into two (xxx and xxxxx).
3) Add a - in the middle
4) Rejoin so it is xxx-xxxxx
5) Run the query above to return my results.

I tried playing with functions and variables but got no-where. Any ideas?

Thanks sincerely everyone.

Rob

 

[jby1]

Split and rejoin could be accomplished using something like:

select left('abcdefgh', 3) + '-' + substring('abcdefgh', 4, 10)

Obviously you would need to play around with it to make it fit your exact requirements.

I don't really understand the rest of your question

 

[JamesLean]

You can also use STUFF for this:

DECLARE @t varchar(10)
SET @t = '12345678'

SELECT STUFF(@t, 4, 0, '-')

--James

 

[jby1]

That looks like it could be a useful function, have a star!