SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Need a Function for this String ....
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

funk.phenomena
Posting Yak Master

Canada
120 Posts

Posted - 01/06/2014 :  14:44:40  Show Profile  Reply with Quote
Hi All - I have the following table with two columns:

SEQ_NUM  STATUS_CODE

101      D12
102      DWI - REGULAR DWW - EXPIRED
103      WRW - REG     WRT - FUTURE

I would like to output the following:


SEQ_NUM  STATUS_CODE

101      D12
102      DWI
102      DWW
103      WRW
103      WRT


The Status code column in the source table will have a single 3 DIGIT alphanumeric code, and sometimes a longer string. I need a code that will automatically grab the 3 digit code before the dash.

Any ideas? Thanks!

Edited by - funk.phenomena on 01/06/2014 15:17:08

hotdog
Starting Member

USA
3 Posts

Posted - 01/06/2014 :  14:55:31  Show Profile  Reply with Quote
SELECT SEQ_NUM, LEFT(STATUS_CODE, 3) AS STATUS_CODE
FROM YourTable

"If you don't have the time to do it right, you definitely don't have the time to do it over."
Go to Top of Page

funk.phenomena
Posting Yak Master

Canada
120 Posts

Posted - 01/06/2014 :  15:05:10  Show Profile  Reply with Quote
Thanks, but I'm trying to capture on the same line ....
Notice how the SEQ_NUM is duplicated? Thanks,
Go to Top of Page

hotdog
Starting Member

USA
3 Posts

Posted - 01/06/2014 :  15:13:42  Show Profile  Reply with Quote
Ohhh. Ok. I see it now. Didn't pay close enough attention the first time to the duplicates.

How many times can the patterns (XXX - status) repeat itself within a single record?

"If you don't have the time to do it right, you definitely don't have the time to do it over."
Go to Top of Page

funk.phenomena
Posting Yak Master

Canada
120 Posts

Posted - 01/06/2014 :  15:16:56  Show Profile  Reply with Quote
Only twice - However the free form text length varies. It can say "regular" or "Reg" and so forth.
Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
965 Posts

Posted - 01/07/2014 :  03:30:39  Show Profile  Reply with Quote

DECLARE @a TABLE(SEQ_NUM INT, STATUS_CODE VARCHAR(50))
INSERT INTO @a SELECT
101, 'D12' UNION ALL SELECT
102, 'DWI - REGULAR DWW - EXPIRED' UNION ALL SELECT
103, 'WRW - REG     WRT - FUTURE'

SELECT SEQ_NUM, tmp.unpvt_Column
FROM @a
CROSS APPLY(SELECT * 
	FROM (	SELECT LEFT(STATUS_CODE, 4)unpvt_Column
		UNION
		SELECT SUBSTRING(StATUS_CODE, CHARINDEX(' - ', STATUS_CODE, 5) - 3, 3)
		)tmp
	WHERE	unpvt_Column <> '')tmp

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/07/2014 :  06:17:50  Show Profile  Reply with Quote
Are you sure that code will always come close to - characetr?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
965 Posts

Posted - 01/07/2014 :  19:51:35  Show Profile  Reply with Quote
Nope, i just try my luck on it =X
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/08/2014 :  05:32:42  Show Profile  Reply with Quote
quote:
Originally posted by waterduck

Nope, i just try my luck on it =X


sorry my question was to OP

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000