| Author |
 Topic  |
|
|
danyeung
Posting Yak Master
102 Posts |
 Posted - 06/18/2012 : 19:22:24
|
In the following, how do I extract data between the 3rd and 4th hyphen in case else? Thanks.
CASE Left(GL.GLAccount, 25)
WHEN 'CPB01-7000000-1910-CP0289' THEN SUBSTRING(GL.GLAccount, 14, 4)
WHEN 'CAN02-7002000-1910-CP0278' THEN SUBSTRING(GL.GLAccount, 14, 4)
ELSE ???
END as GlAccount2, |
|
|
jezemine
Flowing Fount of Yak Knowledge
USA
2871 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47031 Posts |
Posted - 06/18/2012 : 22:48:50
|
only if GLAccount contains maximum of 4 parts
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
danyeung
Posting Yak Master
102 Posts |
Posted - 06/19/2012 : 10:50:53
|
| Parsename parses ".". The string I have is "-". Please advise. |
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2865 Posts |
Posted - 06/19/2012 : 10:56:13
|
Use REPLACE first
PARSENAME(REPLACE(GLAccount,'-','.'),2)
Jim
Everyday I learn something that somebody else already knew |
|
|
|
robvolk
Most Valuable Yak
USA
15557 Posts |
Posted - 06/19/2012 : 10:56:20
|
SELECT PARSENAME(REPLACE(Left(GL.GLAccount, 25),'-','.'),2)
 |
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2865 Posts |
Posted - 06/19/2012 : 10:58:02
|
It took you seven seconds to type SELECT?
Jim
Everyday I learn something that somebody else already knew |
|
|
|
danyeung
Posting Yak Master
102 Posts |
Posted - 06/19/2012 : 11:04:26
|
Yes, I thought about using replace after posted. You guys are quick. :)
However, I got all null with he following.
declare @str as varchar(max)
set @str = 'be CPB01-7000000-1910-CP0289-880-000-254-000'
select parsename(replace(@str, '-', '.'), 1)
select parsename(replace(@str, '-', '.'), 2)
select parsename(replace(@str, '-', '.'), 3)
select parsename(replace(@str, '-', '.'), 4)
select parsename(replace(@str, '-', '.'), 5)
select parsename(replace(@str, '-', '.'), 6) |
Edited by - danyeung on 06/19/2012 11:31:57 |
|
|
|
robvolk
Most Valuable Yak
USA
15557 Posts |
Posted - 06/19/2012 : 11:22:11
|
quote:
Originally posted by jimf
It took you seven seconds to type SELECT?
Hey, six letters and a space, one second each. I put care and thought and uh, what was I gonna say, um, other quality stuff in my typing! |
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2865 Posts |
Posted - 06/19/2012 : 11:49:47
|
quote:
Yes, I thought about using replace after posted. You guys are quick. :)
However, I got all null with he following.
declare @str as varchar(max)
set @str = 'be CPB01-7000000-1910-CP0289-880-000-254-000'
select parsename(replace(@str, '-', '.'), 1)
select parsename(replace(@str, '-', '.'), 2)
select parsename(replace(@str, '-', '.'), 3)
select parsename(replace(@str, '-', '.'), 4)
select parsename(replace(@str, '-', '.'), 5)
select parsename(replace(@str, '-', '.'), 6)
See Visakh's post
quote:
Posted - 06/18/2012 : 22:48:50
--------------------------------------------------------------------------------
only if GLAccount contains maximum of 4 parts
Jim
Everyday I learn something that somebody else already knew |
|
|
|
danyeung
Posting Yak Master
102 Posts |
Posted - 06/19/2012 : 11:55:01
|
quote:
Originally posted by danyeung
I got all null with he following.
declare @str as varchar(max)
set @str = 'be CPB01-7000000-1910-CP0289-880-000-254-000'
select parsename(replace(@str, '-', '.'), 1)
select parsename(replace(@str, '-', '.'), 2)
select parsename(replace(@str, '-', '.'), 3)
select parsename(replace(@str, '-', '.'), 4)
select parsename(replace(@str, '-', '.'), 5)
select parsename(replace(@str, '-', '.'), 6)
This example doesn't use the GL Account. I expect to see "000" for 1, "254" for 2, "000" for 3, "880" for 4, and so on. Instead, I got all null. |
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2865 Posts |
Posted - 06/19/2012 : 11:56:03
|
You could use Visakh's ParseValues function which will turn your string into a table, and then select the value where ID = 4
Jim
Everyday I learn something that somebody else already knew |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47031 Posts |
|
| |
Topic |
|
|
|
How to extract data between 3rd and 4th hyphen
