How to replace a specific part of string contains value in side whole string
-
Good Morning,
CREATE TABLE MyTable (ID int, DATA NVARCHAR(MAX))
INSERT INTO MyTable values (1, 'ID:12345, Name: Chris , Tiger, Sal:3000, City: NYC')
INSERT INTO MyTable values (2, 'ID:23456, Name: Scott Lowski, Sal:4000, City: BOSTON')
INSERT INTO MyTable values (3, 'ID:34567, Name: Mat, Linker Tiger, Sal:1000, City: LONDON')
INSERT INTO MyTable values (4, 'ID:45678, Name: Tiger, Sal:2000, City: NYC')
expected output is just replace comma for Name: value inbetween leave last comma in Name field (if any exist in between names only)
CREATE TABLE MyoutputTable (ID int, DATA NVARCHAR(MAX))
INSERT INTO MyTable values (1, 'ID:12345, Name: Chris Tiger, Sal:3000, City: NYC')
INSERT INTO MyTable values (2, 'ID:23456, Name: Scott Lowski, Sal:4000, City: BOSTON')
INSERT INTO MyTable values (3, 'ID:34567, Name: Mat Linker Tiger, Sal:1000, City: LONDON')
INSERT INTO MyTable values (4, 'ID:45678, Name: Tiger, Sal:2000, City: NYC')
Thank you
Sita
Thank you
Sita
-
I tried Charindex (Name: ) and Charindex(Sal:) then seond - one but no luck yet
Please help me. Thank you
-
Something like this
CREATE TABLE #MyTable (ID int, DATA NVARCHAR(MAX))
INSERT INTO #MyTable values (1, 'ID:12345, Name: Chris , Tiger, Sal:3000, City: NYC')
, (2, 'ID:23456, Name: Scott Lowski, Sal:4000, City: BOSTON')
, (3, 'ID:34567, Name: Mat, Linker Tiger, Sal:1000, City: LONDON')
, (4, 'ID:45678, Name: Tiger, Sal:2000, City: NYC');
SELECT mt.*
, NameVal = REPLACE(LEFT(o.NameVal, LEN(o.NameVal) -1), ',', '') + RIGHT(o.NameVal, 1)
, UpdatedData = REPLACE(mt.DATA, o.NameVal, REPLACE(LEFT(o.NameVal, LEN(o.NameVal) -1), ',', '') + RIGHT(o.NameVal, 1))
FROM #MyTable AS mt
CROSS APPLY (SELECT PATINDEX('%Name:%', mt.DATA) +5) AS n(pos)
CROSS APPLY (SELECT PATINDEX('%Sal:%', mt.DATA)) AS s(pos)
CROSS APPLY (SELECT TRIM(SUBSTRING(mt.DATA, n.pos, s.pos -n.pos))) AS o(NameVal); -
This was removed by the editor as SPAM
- DesNorton wrote:Zond Sita wrote:
I tried Charindex (Name: ) and Charindex(Sal:) then seond - one but no luck yet
Please help me. Thank you
CharIndex only uses a single character. For a group of characters, try PATINDEX
CHARINDEX isn't limited to a single character. It can use a character *expression* up to 8000 characters. The basic differences are:
- PATINDEX uses wildcard's to search for a string pattern.
- CHARINDEX does not allow wildcards.
- CHARINDEX has a third parameter to define start position
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Thank you very much Des
Thank you Very Much Jeffrey Williams, I will dig in more details of the patindex and charindex. Thank you both Again
- Jeffrey Williams wrote:DesNorton wrote:Zond Sita wrote:
I tried Charindex (Name: ) and Charindex(Sal:) then seond - one but no luck yet
Please help me. Thank you
CharIndex only uses a single character. For a group of characters, try PATINDEX
CHARINDEX isn't limited to a single character. It can use a character *expression* up to 8000 characters. The basic differences are:
- PATINDEX uses wildcard's to search for a string pattern.
- CHARINDEX does not allow wildcards.
- CHARINDEX has a third parameter to define start position
Thanks Jeffrey
-
Thank you , Got it Des.
Best Regards
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply