Need a Function for this String ....

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

General SQL Server Forums

New to SQL Server Programming

Need a Function for this String ....

Author

Topic

funk.phenomena
Posting Yak Master

121 Posts

Posted - 2014-01-06 : 14:44:40

Hi All - I have the following table with two columns:
[CODE]
SEQ_NUM STATUS_CODE

101 D12
102 DWI - REGULAR DWW - EXPIRED
103 WRW - REG WRT - FUTURE
[/CODE]
I would like to output the following:

[CODE]
SEQ_NUM STATUS_CODE

101 D12
102 DWI
102 DWW
103 WRW
103 WRT
[/CODE]

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!

hotdog
Starting Member

3 Posts

Posted - 2014-01-06 : 14:55:31

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."

funk.phenomena
Posting Yak Master

121 Posts

Posted - 2014-01-06 : 15:05:10

Thanks, but I'm trying to capture on the same line ....
Notice how the SEQ_NUM is duplicated? Thanks,

hotdog
Starting Member

3 Posts

Posted - 2014-01-06 : 15:13:42

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."

funk.phenomena
Posting Yak Master

121 Posts

Posted - 2014-01-06 : 15:16:56

Only twice - However the free form text length varies. It can say "regular" or "Reg" and so forth.

waterduck
Aged Yak Warrior

982 Posts

Posted - 2014-01-07 : 03:30:39

[code]
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

[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-07 : 06:17:50

Are you sure that code will always come close to - characetr?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

waterduck
Aged Yak Warrior

982 Posts

Posted - 2014-01-07 : 19:51:35

Nope, i just try my luck on it =X

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-08 : 05:32:42

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources