Solved

Replace string in a record

Posted on 2014-11-12
4
152 Views
Last Modified: 2014-11-12
I need to replace the string values  ([ILS-1] CALL: 22-03-18 SPD CALL)  starting in position 15  that is 32 long with the word CALL:
I need to replace the word NORMAL with string  1,1

BEFORE
VMPIC 22:03:18 [ILS-1] CALL: 22-03-18 SPD CALL 71731,9053,9511,NORMAL,11-Nov-2014,21:55:41,21:55:41,22:03:18
VMPIC 22:03:30 [ILS-1] CALL: 22-03-30 SPD CALL 71732,9086,9509,NORMAL,11-Nov-2014,22:01:17,22:01:37,22:03:30

AFTER
VMPIC 22:03:18 CALL: 71731,9053,9511,1,1,11-Nov-2014,21:55:41,21:55:41,22:03:18
VMPIC 22:03:30 CALL: 71732,9086,9509,1,1,11-Nov-2014,22:01:17,22:01:37,22:03:30
0
Comment
Question by:thayduck
  • 2
  • 2
4 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40437902
It's actually at position 16.

See the below code:

declare @mystring nvarchar(200)
set @mystring = 'VMPIC 22:03:18 [ILS-1] CALL: 22-03-18 SPD CALL 71731,9053,9511,NORMAL,11-Nov-2014,21:55:41,21:55:41,22:03:18'

select substring(@mystring,1,15) + N'CALL: ' + replace(substring(@mystring,48,999),'NORMAL','1,1')

set @mystring = 'VMPIC 22:03:30 [ILS-1] CALL: 22-03-30 SPD CALL 71732,9086,9509,NORMAL,11-Nov-2014,22:01:17,22:01:37,22:03:30'

select substring(@mystring,1,15) + N'CALL: ' + replace(substring(@mystring,48,999),'NORMAL','1,1')

Open in new window

0
 

Author Comment

by:thayduck
ID: 40437913
This is a SQL table with 1500+ records all with different times.
0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40437925
Yes, and you have given me no other information to work with, so I work with what you have given me.

Just change @mystring to the name of your field in your table in your query.
0
 

Author Closing Comment

by:thayduck
ID: 40438553
That worked, thanks.
0

Featured Post

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question