in my original database have a column which is for "path" ,the record in this column is like → 【mms://192.12.34.56/2/1/kbe-1a1.wmv】
this kind of column is about 1202,045 .. I don't think is a easy job to update by person.. it may work but have to do same job 1202,045 times..
I have to change 【 mms://192.12.34.56/2/1/kbe-1a1.wav】 to 【 mms://202.11.34.56/2/1/kbe-1a1.wav】
I tried to find the reference book and internet . can't find out the answer for this problem.
can you help? or maybe is it a impossible job?
thanks
As long as you're using an exact match it shouldn't be an issue. (UPDATE TableName SET FieldName = NewFieldValue WHERE FieldName = OldFieldValue)
But, if you're talking about have to fix the path for a lot of different files, you'll have to do either a complex sproc that can parse the path or a small application that pulls the initial set using a LIKE query for the path to be changed and then turns around and does an UPDATE on each record to the new path.
|||thank you
problem is to fix the path but I can't really understand your suggest
can you explain more or just give me a simple example please
I appreciate your help, thank you very much
|||
You can use REPLACE to fix your path value. Here is a sample:
UPDATE pathToChangeSET path =replace(path,'192.12','202.11')
It will replace all 192.12 with 202.11 if you run this query for your table.|||
thank you
sorry I didn't make my question clearly ..
the problem can't be resolved by use replace
for example
in the same column " voice"
some are looks like → mms:// 1.2.3.4/1/2/voice.wma
some are looks like → mms://www.showhigh.com/1/2/voice.wma
also we have others like → "this voice is mms://1.2.3.4/1/2/voice.wma " -- the last kind of data we don't know how many cheracter front of the path...
-----------
do you have any good idea to change the path without manually
thanks again..
|||Do you want to change mms:// 1.2.3.4/, mms://www.showhigh.com/, and this voice is mms://1.2.3.4/ or anything up to the first slash (/) to mms://202.11.34.56/? If this is the case, you need to find the location of this slash and get this job done with a string operation.
Post some sample data and the result you are expecting. It will save time to get result. Thanks.
|||sorry.. not make my question clearly enough still..
here are the example for my question..
in Table name Movie, have a column name "Movie_Voice"
Voice_no | Movie_Voice
1 | mms://1.2.3.4/1/65/05-65-01.wma want change it to be → mms/1.2.3.4/1/65/05-65-01.wma
2 | mms://media.movie.com/12/79/ef13-79-01.wma want change it to be → mms/12/79/ef13-79-01.wma
3 | there are the voice path mms://1.2.3.4/23/230.wma want change it to be → there are the voice path mms/1.2.3.4/23/230.wma
4 | your voice path mms://media.movie.com/98/2/23.wma want change it to be → your voice path mms/98/2/23.wma
------------------------------------------------------------
the voice_no 3 and voice_no 4 are the hardest part for me to think out a way to resolve ,because I don't know what are front of the mms path
the reason why I have to change the data in database . I move all wma files to root and creat a file name mms, so now I have to change all path
which alreday list in database...
thank you
|||
Hi jc,
If these are the only patterns or u have more patterns. If you can provide me a list of pattern i can create a regex to update the column for you which you can use to update the column
Satya
|||unfortunately... I can't list a pattern for this problem
because... the pattern may be up to 4000 or more...
some of tables which including all html tag to 1 column . the column not have the mms path not only have some html tag ..
|||
Can u send me the exported table in a txt file or a mdf file
satya.tanwar@.gmail.com
coz without seeing the patterns nobody will be able to produce a desired solution
Satya
|||thank you , I have just sent !
|||
Hi Try this function,
Select dbo.PatternReplace(TestColumn,'%mms://%/','mms/'),TestColumnfrom pro_Lesson_Text
CREATEFUNCTION dbo.PatternReplace
(
@.InputStringNVARCHAR(MAX),
@.PatternVARCHAR(100),
@.ReplaceTextNVARCHAR(MAX))
RETURNSNVARCHAR(MAX)
AS
BEGIN
DECLARE @.ResultNVARCHAR(MAX)SET @.Result=''
-- First character in a match
DECLARE @.FirstINT
-- Next character to start search on
DECLARE @.NextINTSET @.Next= 1-- Length of the total string -- 8001 if @.InputString is NULL
DECLARE @.LenINTSET @.Len=COALESCE(LEN(@.InputString), 8001)
-- End of a pattern
DECLARE @.EndPatternINT
WHILE(@.Next<= @.Len)
BEGIN
SET @.First=PATINDEX('%'+ @.Pattern+'%',SUBSTRING(@.InputString, @.Next, @.Len))
IFCOALESCE(@.First, 0)= 0--no match - return
BEGIN
SET @.Result= @.Result+CASE--return NULL, just like REPLACE, if inputs are NULL
WHEN @.InputStringISNULL
OR @.PatternISNULL
OR @.ReplaceTextISNULLTHENNULL
ELSESUBSTRING(@.InputString, @.Next, @.Len)
END
BREAK
END
ELSE
BEGIN
-- Concatenate characters before the match to the result
SET @.Result= @.Result+SUBSTRING(@.InputString, @.Next, @.First- 1)
SET @.Next= @.Next+ @.First- 1SET @.EndPattern= 1
-- Find start of end pattern range
WHILEPATINDEX(@.Pattern,SUBSTRING(@.InputString, @.Next, @.EndPattern))= 0SET @.EndPattern= @.EndPattern+ 1-- Find end of pattern range
WHILEPATINDEX(@.Pattern,SUBSTRING(@.InputString, @.Next, @.EndPattern))> 0AND @.Len>=(@.Next+ @.EndPattern- 1)
SET @.EndPattern= @.EndPattern+ 1--Either at the end of the pattern or @.Next + @.EndPattern = @.Len
SET @.Result= @.Result+ @.ReplaceTextSET @.Next= @.Next+ @.EndPattern- 1END
END
RETURN(@.Result)
END
Result looks good Check my mail for the Results. Let me know in case anything else is required
Satya
|||It looks like this would do it as well:
UPDATE Movie
SET Movie_voice=REPLACE(REPLACE(Movie_voice,'mms://media.movie.com/','mms/'),'mms://','mms/')
|||
Hi motley,
Its a good try but the problem is he has different patterns in the data to update. So need to update patterns not static string.
satya
|||
Maybe I misunderstood what he was asking for, but wouldn't the code you gave change:
mms://1.2.3.4/stuff to mms/stufff? And that's not what he showed what he wanted for #1 & #3.
