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.
Author |
Topic |
sonia_newbie
Starting Member
17 Posts |
Posted - 2013-03-10 : 01:32:24
|
I have a sequence :PH01000000G0240 P.he_genemodel_v1.0 CDS 120721 121773 . - . ID=PH01000000G0240.CDS;Parent=PH01000000G0240PH01000001G0190 P.he_genemodel_v1.0 mRA 136867 137309 . - . ID=PH01000001G0190.mRNA;Parent=PH01000001G0190.............................................PH01278028G0010 P.he_genemodel_v1.0 CDS 27 501.. . - . ID=PH01278028G0010;Description="oereed"PH01278104G0010 P.he_genemodel_v1.0 CDS 34 171 . - . ID=PH01278104G0010.CDS;Parent=PH01278104G0010I want to replace PH0100000 by string but only in the first tab likePH01000000 to string0PH01000001 to string1....PH01278104 to string278104PH01278028 to string278028I want it to look likestring0G0240 P.he_genemodel_v1.0 CDS 120721 121773 . - . ID=PH01000000G0240.CDS;Parent=PH01000000G0240string1G0190 P.he_genemodel_v1.0 mRA 136867 137309 . - . ID=PH01000001G0190.mRNA;Parent=PH01000001G0190.............................................string278028G0010 P.he_genemodel_v1.0 CDS 27 501.. . - . ID=PH01278028G0010;Description="oereed"string278104G0010 P.he_genemodel_v1.0 CDS 34 171 . - . ID=PH01278104G0010.CDS;Parent=PH01278104G0010I used sed command but It replaced every occurence oF PH01000000 to string0, I only want replacement only in first tab.Also my command repalced PH01000000 to string00000 but i want PH01000000 to string0 remove the extra digits.Is this possible?Thanks |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-10 : 04:08:13
|
Is the above content of a single column or are they under multiple columns?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
sonia_newbie
Starting Member
17 Posts |
Posted - 2013-03-10 : 10:11:09
|
These data are seperated by a tab!! |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-03-10 : 11:37:56
|
[code]STUFF(YourColumnName,1,CHARINDEX(CHAR(9),YourColumnName),'')[/code] |
|
|
sonia_newbie
Starting Member
17 Posts |
Posted - 2013-03-10 : 12:58:47
|
Will this coommand replace my entries by string? |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-03-10 : 13:10:48
|
Ah.. what I posted will remove the part before the first tab.When you want to replace, what is the rule that you want to use? It seems like you want to have a fixed length to the number that is appended? For example PH01278104 --> string278104. Why is the 01 omitted? Is it because you want to keep only six digits, or is it something else? |
|
|
sonia_newbie
Starting Member
17 Posts |
Posted - 2013-03-10 : 14:56:23
|
I want to replace PH0100000 by string but only in the first tab likePH01000000 to string0PH01000001 to string1....PH01278104 to string278104PH01278028 to string278028"stringvalue" is the format i need. so i have to remove the PH01 from all entries but if used replace PH01 for all entries PH01000000 beocmes string00000 instead of string0.I hope i am not confusing.Thanks |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-03-12 : 06:13:07
|
DECLARE @tab TABLE( col VARCHAR(30))insert into @tabSELECT 'PH01000000G0240' UNION ALL -- String0G0240SELECT 'PH01000001G0190' UNION ALL -- String1G0190SELECT 'PH01278104G0010' UNION ALL String278104G0010SELECT 'PH01278028G0210' --String278028G0210SELECT 'String' +CAST(CAST(SUBSTRING(col, 5, 6) AS INT) AS VARCHAR(10))+ RIGHT(col, 5) FROM @tab--Chandu |
|
|
|
|
|