Solved

Replace string in a record

Posted on 2014-11-12
4
132 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
Comment Utility
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
Comment Utility
This is a SQL table with 1500+ records all with different times.
0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
Comment Utility
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
Comment Utility
That worked, thanks.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

771 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now