Parse the string

Question

Post reply

Parse the string

MyTSQLRepl

SSC Eights!

Points: 824
More actions
July 17, 2007 at 6:39 am

#117847

Hello All,
I have a temp table with a column which needs to be formattted. The column has following values:
2.0\ABC
1.4
1.2\PQR
2.2\XYZ
1.1
I need to format this column as
2.0(ABC)
1.4
1.2(PQR)
2.2(XYZ)
1.1
If there is a backslash then the string followed by that showed appear in parenthesis.
Can anyone tell me how to do it?
Thanks in advace.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jez-448386 SSCarpal Tunnel Points: 4594 More actions · Answer 1

declare @v-2 varchar(255)

set @v-2 = '2.0\ABC'

select replace(@v, '\', '(')

+ case

when charindex('\', @v-2) > 0 then ')'

else ''

end

J

MyTSQLRepl SSC Eights! Points: 824 More actions · Answer 2

Thanks for the quick reply Jeremy.

I am already parsing the string to get these strings. The actual values in the database column 'columnID' are

\\CDR\HR Project\GMP 2.0\ABC

\\CDR\HR Project\GMP 1.4

\\CDR\HR Project\GMP 1.2\PQR

\\CDR\HR Project\GMP 2.2\XYZ

\\CDR\HR Project\GMP 1.1

I use the following sql

select replace(columnID,left(columnID,charindex('.',columnID)-2),'') AS IDPath

which parses to

2.0\ABC

1.4

1.2\PQR

2.2\XYZ

1.1

But now I need as final output

2.0(ABC)

1.4

1.2(PQR)

2.2(XYZ)

1.1

How would I combine your suggestion to my earlier parse function to get the final output

Thanks in advance

Jez-448386 SSCarpal Tunnel Points: 4594 More actions · Answer 3

just nest your replace into mine:

declare @t table

( columnId varchar(255))

insert @t select '\\CDR\HR Project\GMP 2.0\ABC'

insert @t select '\\CDR\HR Project\GMP 1.4'

insert @t select '\\CDR\HR Project\GMP 1.2\PQR'

insert @t select '\\CDR\HR Project\GMP 2.2\XYZ'

insert @t select '\\CDR\HR Project\GMP 1.1'

select replace(replace(columnId,left(columnId,charindex('.',columnId)-2),''), '\', '(')

+ case

when charindex('\', replace(columnId,left(columnId,charindex('.',columnId)-2),'')) > 0 then ')'

else ''

end

from @t

J

MyTSQLRepl SSC Eights! Points: 824 More actions · Answer 4

Thanks Jeremy, you suggestion worked exactly the way I wanted it to be.

Thanks again