问:
Golang ticker concurrency issue
I have a function written in Golang as follows

```<code>func (participant *SimulationParticipant) StartTransactionsGatewayTicker() {
//Gateway
logging.InfoLogger.Printf("StartTransactionsGatewayTicker:%v", participant.Participant)
ticker := time.NewTicker(1 * time.Second)
participant.TransactionGatewayTicker = ticker
go func() {
    for {
        select {
        case <-ticker.C:
            logging.InfoLogger.Printf("Tick at: %v", participant.Participant)
            participant.GetTransactions()
        }
    }
}()
}
</code>```

I am calling the function in a loop as with 2 SimulationParticipant in the array.
surprisingly the first participant is replaced by second participant and the GetTransactions always getting executed to the last item in the loop? How can I overcome this

答:
Works for me (I am posting this code without seeing how you are calling StartTransactionsGatewayTicker, Donot downvote if not applicable :P ) :

```<code>// [Timers](timers) are for when you want to do
// something once in the future - _tickers_ are for when
// you want to do something repeatedly at regular
// intervals. Here's an example of a ticker that ticks
// periodically until we stop it.

package main

import (
    "fmt"
    "time"
)

func main() {

        part1 := SimulationParticipant{}
    part1.id = "part1"
        part2 := SimulationParticipant{}
        part2.id = "part2"
        partSlice := make([]*SimulationParticipant,0)
        partSlice = append(partSlice, &part1, &part2)

        for _ , p := range partSlice {
             p.StartTransactionsGatewayTicker()
        }

    // Tickers can be stopped like timers. Once a ticker
    // is stopped it won't receive any more values on its
    // channel. We'll stop ours after 16000ms.
    time.Sleep(16000 * time.Millisecond)
    part1.ticker.Stop()
    part2.ticker.Stop()
    fmt.Println("Ticker stopped")
}

type SimulationParticipant struct {
     id string
     ticker *time.Ticker
}

func (participant *SimulationParticipant) StartTransactionsGatewayTicker() {

ticker := time.NewTicker(1 * time.Second)
participant.ticker = ticker
go func() {
    for {
        select {
        case t := <-ticker.C:
            fmt.Println("Tick at", t,participant.id)
        }
    }
}()
}

</code>```

Output :

```<code>Tick at 2009-11-10 23:00:01 +0000 UTC m=+1.000000001 part2
Tick at 2009-11-10 23:00:01 +0000 UTC m=+1.000000001 part1
Tick at 2009-11-10 23:00:02 +0000 UTC m=+2.000000001 part1
Tick at 2009-11-10 23:00:02 +0000 UTC m=+2.000000001 part2
Tick at 2009-11-10 23:00:03 +0000 UTC m=+3.000000001 part2
Tick at 2009-11-10 23:00:03 +0000 UTC m=+3.000000001 part1
Tick at 2009-11-10 23:00:04 +0000 UTC m=+4.000000001 part1
Tick at 2009-11-10 23:00:04 +0000 UTC m=+4.000000001 part2
Tick at 2009-11-10 23:00:05 +0000 UTC m=+5.000000001 part2
Tick at 2009-11-10 23:00:05 +0000 UTC m=+5.000000001 part1
Tick at 2009-11-10 23:00:06 +0000 UTC m=+6.000000001 part1
Tick at 2009-11-10 23:00:06 +0000 UTC m=+6.000000001 part2
Tick at 2009-11-10 23:00:07 +0000 UTC m=+7.000000001 part2
Tick at 2009-11-10 23:00:07 +0000 UTC m=+7.000000001 part1
Tick at 2009-11-10 23:00:08 +0000 UTC m=+8.000000001 part1
Tick at 2009-11-10 23:00:08 +0000 UTC m=+8.000000001 part2
Tick at 2009-11-10 23:00:09 +0000 UTC m=+9.000000001 part2
Tick at 2009-11-10 23:00:09 +0000 UTC m=+9.000000001 part1
Tick at 2009-11-10 23:00:10 +0000 UTC m=+10.000000001 part1
Tick at 2009-11-10 23:00:10 +0000 UTC m=+10.000000001 part2
Tick at 2009-11-10 23:00:11 +0000 UTC m=+11.000000001 part2
Tick at 2009-11-10 23:00:11 +0000 UTC m=+11.000000001 part1
Tick at 2009-11-10 23:00:12 +0000 UTC m=+12.000000001 part1
Tick at 2009-11-10 23:00:12 +0000 UTC m=+12.000000001 part2
Tick at 2009-11-10 23:00:13 +0000 UTC m=+13.000000001 part2
Tick at 2009-11-10 23:00:13 +0000 UTC m=+13.000000001 part1
Tick at 2009-11-10 23:00:14 +0000 UTC m=+14.000000001 part1
Tick at 2009-11-10 23:00:14 +0000 UTC m=+14.000000001 part2
Tick at 2009-11-10 23:00:15 +0000 UTC m=+15.000000001 part2
Tick at 2009-11-10 23:00:15 +0000 UTC m=+15.000000001 part1
Ticker stopped
</code>```

Playground : https://play.golang.org/p/yfHnrRK1iG8

Title: Golang ticker concurrency issue

Body:
I have a function written in Golang as follows

func (participant *SimulationParticipant) StartTransactionsGatewayTicker() {
//Gateway
logging.InfoLogger.Printf("StartTransactionsGatewayTicker:%v", participant.Participant)
ticker := time.NewTicker(1 * time.Second)
participant.TransactionGatewayTicker = ticker
go func() {
    for {
        select {
        case <-ticker.C:
            logging.InfoLogger.Printf("Tick at: %v", participant.Participant)
            participant.GetTransactions()
        }
    }
}()
}

I am calling the function in a loop as with 2 SimulationParticipant in the array.
surprisingly the first participant is replaced by second participant and the GetTransactions always getting executed to the last item in the loop? How can I overcome this

Body:
Works for me (I am posting this code without seeing how you are calling StartTransactionsGatewayTicker, Donot downvote if not applicable :P ) :

// [Timers](timers) are for when you want to do
// something once in the future - _tickers_ are for when
// you want to do something repeatedly at regular
// intervals. Here's an example of a ticker that ticks
// periodically until we stop it.

package main

import (
    "fmt"
    "time"
)

func main() {

        part1 := SimulationParticipant{}
    part1.id = "part1"
        part2 := SimulationParticipant{}
        part2.id = "part2"
        partSlice := make([]*SimulationParticipant,0)
        partSlice = append(partSlice, &part1, &part2)

        for _ , p := range partSlice {
             p.StartTransactionsGatewayTicker()
        }

    // Tickers can be stopped like timers. Once a ticker
    // is stopped it won't receive any more values on its
    // channel. We'll stop ours after 16000ms.
    time.Sleep(16000 * time.Millisecond)
    part1.ticker.Stop()
    part2.ticker.Stop()
    fmt.Println("Ticker stopped")
}

type SimulationParticipant struct {
     id string
     ticker *time.Ticker
}

func (participant *SimulationParticipant) StartTransactionsGatewayTicker() {

ticker := time.NewTicker(1 * time.Second)
participant.ticker = ticker
go func() {
    for {
        select {
        case t := <-ticker.C:
            fmt.Println("Tick at", t,participant.id)
        }
    }
}()
}

Output :

Tick at 2009-11-10 23:00:01 +0000 UTC m=+1.000000001 part2
Tick at 2009-11-10 23:00:01 +0000 UTC m=+1.000000001 part1
Tick at 2009-11-10 23:00:02 +0000 UTC m=+2.000000001 part1
Tick at 2009-11-10 23:00:02 +0000 UTC m=+2.000000001 part2
Tick at 2009-11-10 23:00:03 +0000 UTC m=+3.000000001 part2
Tick at 2009-11-10 23:00:03 +0000 UTC m=+3.000000001 part1
Tick at 2009-11-10 23:00:04 +0000 UTC m=+4.000000001 part1
Tick at 2009-11-10 23:00:04 +0000 UTC m=+4.000000001 part2
Tick at 2009-11-10 23:00:05 +0000 UTC m=+5.000000001 part2
Tick at 2009-11-10 23:00:05 +0000 UTC m=+5.000000001 part1
Tick at 2009-11-10 23:00:06 +0000 UTC m=+6.000000001 part1
Tick at 2009-11-10 23:00:06 +0000 UTC m=+6.000000001 part2
Tick at 2009-11-10 23:00:07 +0000 UTC m=+7.000000001 part2
Tick at 2009-11-10 23:00:07 +0000 UTC m=+7.000000001 part1
Tick at 2009-11-10 23:00:08 +0000 UTC m=+8.000000001 part1
Tick at 2009-11-10 23:00:08 +0000 UTC m=+8.000000001 part2
Tick at 2009-11-10 23:00:09 +0000 UTC m=+9.000000001 part2
Tick at 2009-11-10 23:00:09 +0000 UTC m=+9.000000001 part1
Tick at 2009-11-10 23:00:10 +0000 UTC m=+10.000000001 part1
Tick at 2009-11-10 23:00:10 +0000 UTC m=+10.000000001 part2
Tick at 2009-11-10 23:00:11 +0000 UTC m=+11.000000001 part2
Tick at 2009-11-10 23:00:11 +0000 UTC m=+11.000000001 part1
Tick at 2009-11-10 23:00:12 +0000 UTC m=+12.000000001 part1
Tick at 2009-11-10 23:00:12 +0000 UTC m=+12.000000001 part2
Tick at 2009-11-10 23:00:13 +0000 UTC m=+13.000000001 part2
Tick at 2009-11-10 23:00:13 +0000 UTC m=+13.000000001 part1
Tick at 2009-11-10 23:00:14 +0000 UTC m=+14.000000001 part1
Tick at 2009-11-10 23:00:14 +0000 UTC m=+14.000000001 part2
Tick at 2009-11-10 23:00:15 +0000 UTC m=+15.000000001 part2
Tick at 2009-11-10 23:00:15 +0000 UTC m=+15.000000001 part1
Ticker stopped

Playground : https://play.golang.org/p/yfHnrRK1iG8


来源: stackexchange

问:
How to make a stopwatch timer
For some reason the stopwatch looks to be alright, but when compared to the real deal it goes way too fast. I don't understand what I should do. Is it the timeInterval that I should change? I would like it to show millisecond, second and minutes, but everything I try just makes it worse.. Thank you</p>

```<code>import UIKit
class ViewController: UIViewController {

    // Outlets
    @IBOutlet weak var minutes: UILabel!
    @IBOutlet weak var seconds: UILabel!
    @IBOutlet weak var milliSecondsLabel: UILabel!

    @IBOutlet weak var resetButton: UIButton!
    @IBOutlet weak var startButton: UIButton!
    @IBOutlet weak var pauseButton: UIButton!

    // Variables
    var timer = Timer()
    var time: Int = 0
    var running: Bool = false

    override func viewDidLoad() {
        super.viewDidLoad()
        // Do any additional setup after loading the view.
        roundButtons()

    }

    @IBAction func resetTimer(_ sender: Any) {
        timer.invalidate()
        time = 0
        updateUI()
        running = false
    }

    @IBAction func startTimer(_ sender: Any) {
        if running {
            return
        } else {
            timer = Timer.scheduledTimer(timeInterval: 0.01, target: self, selector: #selector(timerDidEnd), userInfo: nil, repeats: true)
            running = true
        }

    }

    @IBAction func pauseTimer(_ sender: Any) {
        timer.invalidate()
        running = false
    }

    func roundButtons() {
        resetButton.layer.cornerRadius = resetButton.frame.height / 2
        startButton.layer.cornerRadius = startButton.frame.height / 2
        pauseButton.layer.cornerRadius = pauseButton.frame.height / 2
    }

    @objc func timerDidEnd() {
        time += 1
        updateUI()
    }

    func updateUI() {

        var min: Int
        var sec: Int
        var mil: Int

        min = time / (60*60)
        sec = (time/60)%60
        mil = time & 60

        minutes.text = String(min)
        seconds.text = String(sec)
        milliSecondsLabel.text = String(mil)
    }
}
</code>```

答:
I'd suggest two things:

Don’t try to count the time elapsed yourself. Capture the start time and calculate the elapsed time from that. You can use Date method timeIntervalSince, or, because that's not guaranteed to return monotonically increasing values, use CACurrentMediaTime, like below.

Rather than having a timer with an arbitrary 100 updates per second, instead use a CADisplayLink, which is optimally timed for device screen refresh rates.

E.g.:

```<code>class ViewController: UIViewController {

    // Outlets
    @IBOutlet weak var minutesLabel: UILabel!
    @IBOutlet weak var secondsLabel: UILabel!
    @IBOutlet weak var milliSecondsLabel: UILabel!

    @IBOutlet weak var resetButton: UIButton!
    @IBOutlet weak var startButton: UIButton!
    @IBOutlet weak var pauseButton: UIButton!

    // Variables
    private weak var displayLink: CADisplayLink?
    private var startTime: CFTimeInterval?
    private var elapsed: CFTimeInterval = 0
    private var priorElapsed: CFTimeInterval = 0

    override func viewDidLoad() {
        super.viewDidLoad()
        setFonts()
    }

    override func viewDidLayoutSubviews() {
        super.viewDidLayoutSubviews()
        roundButtons()
    }

    @IBAction func resetTimer(_ sender: Any) {
        stopDisplayLink()
        elapsed = 0
        priorElapsed = 0
        updateUI()
    }

    @IBAction func startTimer(_ sender: Any) {
        if displayLink == nil {
            startDisplayLink()
        }
    }

    @IBAction func pauseTimer(_ sender: Any) {
        priorElapsed += elapsed
        elapsed = 0
        displayLink?.invalidate()
    }
}

private extension ViewController {
    func startDisplayLink() {
        startTime = CACurrentMediaTime()
        let displayLink = CADisplayLink(target: self, selector: #selector(handleDisplayLink(_:)))
        displayLink.add(to: .main, forMode: .common)
        self.displayLink = displayLink
    }

    func stopDisplayLink() {
        displayLink?.invalidate()
    }

    @objc func handleDisplayLink(_ displayLink: CADisplayLink) {
        guard let startTime = startTime else { return }
        elapsed = CACurrentMediaTime() - startTime
        updateUI()
    }

    func updateUI() {
        let totalElapsed = elapsed + priorElapsed

        let hundredths = Int((totalElapsed * 100).rounded())
        let (minutes, hundredthsOfSeconds) = hundredths.quotientAndRemainder(dividingBy: 60 * 100)
        let (seconds, milliseconds) = hundredthsOfSeconds.quotientAndRemainder(dividingBy: 100)

        minutesLabel.text = String(minutes)
        secondsLabel.text = String(format: "%02d", seconds)
        milliSecondsLabel.text = String(format: "%02d", milliseconds)
    }

    func roundButtons() {
        resetButton.layer.cornerRadius = resetButton.bounds.height / 2
        startButton.layer.cornerRadius = startButton.bounds.height / 2
        pauseButton.layer.cornerRadius = pauseButton.bounds.height / 2
    }

    func setFonts() {
        minutesLabel.font = UIFont.monospacedDigitSystemFont(ofSize: minutesLabel.font.pointSize, weight: .regular)
        secondsLabel.font = UIFont.monospacedDigitSystemFont(ofSize: secondsLabel.font.pointSize, weight: .regular)
        milliSecondsLabel.font = UIFont.monospacedDigitSystemFont(ofSize: milliSecondsLabel.font.pointSize, weight: .regular)
    }
}
</code>```

That yields:

Completely unrelated, but anything dependent upon the size of views (e.g. the corner rounding) really belongs in viewDidLayoutSubviews, not viewDidLoad.

ContentLicense: CC BY-SA 4.0

Title: How to make a stopwatch timer

Body:
For some reason the stopwatch looks to be alright, but when compared to the real deal it goes way too fast. I don't understand what I should do. Is it the timeInterval that I should change? I would like it to show millisecond, second and minutes, but everything I try just makes it worse.. Thank you

import UIKit
class ViewController: UIViewController {

    // Outlets
    @IBOutlet weak var minutes: UILabel!
    @IBOutlet weak var seconds: UILabel!
    @IBOutlet weak var milliSecondsLabel: UILabel!

    @IBOutlet weak var resetButton: UIButton!
    @IBOutlet weak var startButton: UIButton!
    @IBOutlet weak var pauseButton: UIButton!

    // Variables
    var timer = Timer()
    var time: Int = 0
    var running: Bool = false

    override func viewDidLoad() {
        super.viewDidLoad()
        // Do any additional setup after loading the view.
        roundButtons()

    }

    @IBAction func resetTimer(_ sender: Any) {
        timer.invalidate()
        time = 0
        updateUI()
        running = false
    }

    @IBAction func startTimer(_ sender: Any) {
        if running {
            return
        } else {
            timer = Timer.scheduledTimer(timeInterval: 0.01, target: self, selector: #selector(timerDidEnd), userInfo: nil, repeats: true)
            running = true
        }

    }

    @IBAction func pauseTimer(_ sender: Any) {
        timer.invalidate()
        running = false
    }

    func roundButtons() {
        resetButton.layer.cornerRadius = resetButton.frame.height / 2
        startButton.layer.cornerRadius = startButton.frame.height / 2
        pauseButton.layer.cornerRadius = pauseButton.frame.height / 2
    }

    @objc func timerDidEnd() {
        time += 1
        updateUI()
    }

    func updateUI() {

        var min: Int
        var sec: Int
        var mil: Int

        min = time / (60*60)
        sec = (time/60)%60
        mil = time & 60

        minutes.text = String(min)
        seconds.text = String(sec)
        milliSecondsLabel.text = String(mil)
    }
}

Body:
I'd suggest two things:

Don’t try to count the time elapsed yourself. Capture the start time and calculate the elapsed time from that. You can use Date method timeIntervalSince, or, because that's not guaranteed to return monotonically increasing values, use CACurrentMediaTime, like below.

Rather than having a timer with an arbitrary 100 updates per second, instead use a CADisplayLink, which is optimally timed for device screen refresh rates.

E.g.:

class ViewController: UIViewController {

    // Outlets
    @IBOutlet weak var minutesLabel: UILabel!
    @IBOutlet weak var secondsLabel: UILabel!
    @IBOutlet weak var milliSecondsLabel: UILabel!

    @IBOutlet weak var resetButton: UIButton!
    @IBOutlet weak var startButton: UIButton!
    @IBOutlet weak var pauseButton: UIButton!

    // Variables
    private weak var displayLink: CADisplayLink?
    private var startTime: CFTimeInterval?
    private var elapsed: CFTimeInterval = 0
    private var priorElapsed: CFTimeInterval = 0

    override func viewDidLoad() {
        super.viewDidLoad()
        setFonts()
    }

    override func viewDidLayoutSubviews() {
        super.viewDidLayoutSubviews()
        roundButtons()
    }

    @IBAction func resetTimer(_ sender: Any) {
        stopDisplayLink()
        elapsed = 0
        priorElapsed = 0
        updateUI()
    }

    @IBAction func startTimer(_ sender: Any) {
        if displayLink == nil {
            startDisplayLink()
        }
    }

    @IBAction func pauseTimer(_ sender: Any) {
        priorElapsed += elapsed
        elapsed = 0
        displayLink?.invalidate()
    }
}

private extension ViewController {
    func startDisplayLink() {
        startTime = CACurrentMediaTime()
        let displayLink = CADisplayLink(target: self, selector: #selector(handleDisplayLink(_:)))
        displayLink.add(to: .main, forMode: .common)
        self.displayLink = displayLink
    }

    func stopDisplayLink() {
        displayLink?.invalidate()
    }

    @objc func handleDisplayLink(_ displayLink: CADisplayLink) {
        guard let startTime = startTime else { return }
        elapsed = CACurrentMediaTime() - startTime
        updateUI()
    }

    func updateUI() {
        let totalElapsed = elapsed + priorElapsed

        let hundredths = Int((totalElapsed * 100).rounded())
        let (minutes, hundredthsOfSeconds) = hundredths.quotientAndRemainder(dividingBy: 60 * 100)
        let (seconds, milliseconds) = hundredthsOfSeconds.quotientAndRemainder(dividingBy: 100)

        minutesLabel.text = String(minutes)
        secondsLabel.text = String(format: "%02d", seconds)
        milliSecondsLabel.text = String(format: "%02d", milliseconds)
    }

    func roundButtons() {
        resetButton.layer.cornerRadius = resetButton.bounds.height / 2
        startButton.layer.cornerRadius = startButton.bounds.height / 2
        pauseButton.layer.cornerRadius = pauseButton.bounds.height / 2
    }

    func setFonts() {
        minutesLabel.font = UIFont.monospacedDigitSystemFont(ofSize: minutesLabel.font.pointSize, weight: .regular)
        secondsLabel.font = UIFont.monospacedDigitSystemFont(ofSize: secondsLabel.font.pointSize, weight: .regular)
        milliSecondsLabel.font = UIFont.monospacedDigitSystemFont(ofSize: milliSecondsLabel.font.pointSize, weight: .regular)
    }
}

That yields:

Completely unrelated, but anything dependent upon the size of views (e.g. the corner rounding) really belongs in viewDidLayoutSubviews, not viewDidLoad.


问:
PLSQL to TSQL - REGEXP
Im trying to convert a script from PLSQL to TSQL and am stuff with a couple of lines

```<code>table(cast(multiset(select level from dual connect by level <= len (regexp_replace(t.image, '[^**]+'))/2) as sys.OdciNumberList)) levels

where substr(REGEXP_SUBSTR (t.image,  '[^**]+',1, levels.column_value),1,instr( REGEXP_SUBSTR (t.image,  '[^**]+',1, levels.column_value),'=',1) -1)
</code>```

IMAGE

Any help would be great.

Chris

答:
For a better answer it would be good to include some sample input and desired results. Especially when addressing a different version of SQL. Perhaps including a PL/SQL tag would help find someone who understands PL/SQL and T-SQL. It would also be helpful to include DDL, specifically the datatype for "Level". Again, I say this not to be critical but rather guide you towards getting better answers here.

All That said, you can accomplish what you are trying to do in T-SQL leveraging a tally table, an N-Grams function and a couple other functions which I are included at the end of this post.

regexp_replace

To replace or remove characters that match a pattern in t-SQL you can use patreplace8k. Here's an example of how to use it to replace numbers with *'s:

```<code>SELECT pr.NewString
FROM  samd.patReplace8K('My phone number is 555-2211','[0-9]','*') AS pr;
</code>```

Returns: My phone number is -*

regexp_subsr

Here's an example of how to extract all phone numbers from a string:

```<code>DECLARE
  @string  VARCHAR(8000)  = 'Call me later at 222-3333 or tomorrow at 312.555.2222,
                             (313)555-6789, or at 1+800-555-4444 before noon. Thanks!',
  @pattern VARCHAR(50)    = '%[^0-9()+.-]%';

-- EXTRACTOR
SELECT ItemNumber = ROW_NUMBER() OVER (ORDER BY f.position),
       ItemIndex  = f.position,
       ItemLength = itemLen.l,
       Item       = SUBSTRING(f.token, 1, itemLen.l)
FROM
(
 SELECT ng.position, SUBSTRING(@string,ng.position,DATALENGTH(@string))
 FROM   samd.NGrams8k(@string, 1) AS ng
 WHERE  PATINDEX(@pattern, ng.token) <  --<< this token does NOT match the pattern
        ABS(SIGN(ng.position-1)-1) +    --<< are you the first row?  OR
        PATINDEX(@pattern,SUBSTRING(@string,ng.position-1,1)) --<< always 0 for 1st row
) AS f(position, token)
CROSS APPLY (VALUES(ISNULL(NULLIF(PATINDEX(@pattern,f.token),0), --CROSS APPLY (VALUES(ISNULL(NULLIF(PATINDEX('%'+@pattern+'%',f.token),0),
  DATALENGTH(@string)+2-f.position)-1)) AS itemLen(l)
WHERE    itemLen.L > 6 -- this filter is more harmful to the extractor than the splitter
ORDER BY ItemNumber;
</code>```

T-SQL INSTR Function

I included a T-SQL version of Oracles INSTR function at the end of this post. Note these examples:

```<code>DECLARE
  @string    VARCHAR(8000) = 'AABBCC-AA123-AAXYZPDQ-AA-54321',
  @search    VARCHAR(8000) = '-AA',
  @position  INT           = 1,
  @occurance INT           = 2;

-- 1.1. Get me the 2nd @occurance "-AA" in @string beginning at @position 1
SELECT f.* FROM samd.instr8k(@string,@search,@position,@occurance) AS f;

-- 1.2. Retreive everything *BEFORE* the second instance of "-AA"
SELECT
  ItemIndex = f.ItemIndex,
  Item      = SUBSTRING(@string,1,f.itemindex-1)
FROM samd.instr8k(@string,@search,@position,@occurance) AS f;

-- 1.3. Retreive everything *AFTER* the second instance of "-AA"
SELECT
  ItemIndex = MAX(f.ItemIndex),
  Item      = MAX(SUBSTRING(@string,f.itemindex+f.itemLength,8000))
FROM   samd.instr8k(@string,@search,@position,@occurance) AS f;
</code>```

regexp_replace (ADVANCED)

Here's a more complex example, leveraging ngrams8k to replace phone numbers with the text "REMOVED"

```<code>DECLARE
  @string  VARCHAR(8000)  = 'Call me later at 222-3333 or tomorrow at 312.555.2222, (313)555-6789, or at 1+800-555-4444 before noon. Thanks!',
  @pattern VARCHAR(50)    = '%[0-9()+.-]%';

SELECT NewString = (
  SELECT IIF(IsMatch=1 AND patSplit.item LIKE '%[0-9][0-9][0-9]%','<REMOVED>', patSplit.item)
  FROM
  (
    SELECT 1, i.Idx, SUBSTRING(@string,1,i.Idx), CAST(0 AS BIT)
    FROM   (VALUES(PATINDEX(@pattern,@string)-1)) AS i(Idx) --FROM   (VALUES(PATINDEX('%'+@pattern+'%',@string)-1)) AS i(Idx)
    WHERE  SUBSTRING(@string,1,1) NOT LIKE @pattern
    UNION ALL
    SELECT r.RN,
           itemLength = LEAD(r.RN,1,DATALENGTH(@string)+1) OVER (ORDER BY r.RN)-r.RN,
           item       = SUBSTRING(@string,r.RN,
                        LEAD(r.RN,1,DATALENGTH(@string)+1) OVER (ORDER BY r.RN)-r.RN),
           isMatch    = ABS(t.p-2+1)
    FROM   core.rangeAB(1,DATALENGTH(@string),1,1) AS r
    CROSS APPLY (VALUES (
      CAST(PATINDEX(@pattern,SUBSTRING(@string,r.RN,1))   AS BIT),
      CAST(PATINDEX(@pattern,SUBSTRING(@string,r.RN-1,1)) AS BIT),
      SUBSTRING(@string,r.RN,r.Op+1))) AS t(c,p,s)
    WHERE t.c^t.p = 1
  ) AS patSplit(ItemIndex, ItemLength, Item, IsMatch)
  FOR XML PATH(''), TYPE).value('.','varchar(8000)');
</code>```

Returns:

Call me later at  or tomorrow at , , or at  before noon. Thanks!

```CREATE FUNCTION core.rangeAB
(
  @Low  BIGINT, -- (start) Lowest  number in the set
  @High BIGINT, -- (stop)  Highest number in the set
  @Gap  BIGINT, -- (step)  Difference between each number in the set
  @Row1 BIT     -- Base: 0 or 1; should RN begin with 0 or 1?
)
/****************************************************************************************
[Purpose]:
 Creates a lazy, in-memory, forward-ordered sequence of up to 531,441,000,000 integers
 starting with @Low and ending with @High (inclusive). RangeAB is a pure, 100% set-based
 alternative to solving SQL problems using iterative methods such as loops, cursors and
 recursive CTEs. RangeAB is based on Itzik Ben-Gan's getnums function for producing a
 sequence of integers and uses logic from Jeff Moden's fnTally function which includes a
 parameter for determining if the "row-number" (RN) should begin with 0 or 1.

 I wanted to use the name "Range" because it functions and performs almost identically to
 the Range function built into Python and Clojure. RANGE is a reserved SQL keyword so I
 went with "RangeAB". Functions/Algorithms developed using rangeAB can be easilty ported
 over to Python, Clojure or any other programming language that leverages a lazy sequence.
 The two major differences between RangeAB and the Python/Clojure versions are:
   1. RangeAB is *Inclusive* where the other two are *Exclusive". range(0,3) in Python and
      Clojure return [0 1 2], core.rangeAB(0,3) returns [0 1 2 3].
   2. RangeAB has a fourth Parameter (@Row1) to determine if RN should begin with 0 or 1.

[Author]:
 Alan Burstein

[Compatibility]:
 SQL Server 2008+

[Syntax]:
 SELECT r.RN, r.OP, r.N1, r.N2
 FROM   core.rangeAB(@Low,@High,@Gap,@Row1) AS r;

[Parameters]:
 @Low  = BIGINT; represents the lowest  value for N1.
 @High = BIGINT; represents the highest value for N1.
 @Gap  = BIGINT; represents how much N1 and N2 will increase each row. @Gap is also the
                 difference between N1 and N2.
 @Row1 = BIT;    represents the base (first) value of RN. When @Row1 = 0, RN begins with 0,
                 when @row = 1 then RN begins with 1.

[Returns]:
 Inline Table Valued Function returns:
 RN = BIGINT; a row number that works just like T-SQL ROW_NUMBER() except that it can
      start at 0 or 1 which is dictated by @Row1. If you need the numbers:
      (0 or 1) through @High, then use RN as your "N" value, ((@Row1=0 for 0, @Row1=1),
      otherwise use N1.
 OP = BIGINT; returns the "finite opposite" of RN. When RN begins with 0 the first number
      in the set will be 0 for RN, the last number in will be 0 for OP. When returning the
      numbers 1 to 10, 1 to 10 is retrurned in ascending order for RN and in descending
      order for OP.
      Given the Numbers 1 to 3, 3 is the opposite of 1, 2 the opposite of 2, and 1 is the
      opposite of 3. Given the numbers -1 to 2, the opposite of -1 is 2, the opposite of 0
      is 1, and the opposite of 1 is 0.
      The best practie is to only use OP when @Gap > 1; use core.O instead. Doing so will
      improve performance by 1-2% (not huge but every little bit counts)
 N1 = BIGINT; This is the "N" in your tally table/numbers function. this is your *Lazy*
      sequence of numbers starting at @Low and incrementing by @Gap until the next number
      in the sequence is greater than @High.
 N2 = BIGINT; a lazy sequence of numbers starting @Low+@Gap and incrementing by @Gap. N2
      will always be greater than N1 by @Gap. N2 can also be thought of as:
      LEAD(N1,1,N1+@Gap) OVER (ORDER BY RN)

[Dependencies]:
 N/A

[Developer Notes]:
 1.  core.rangeAB returns one billion rows in exactly 90 seconds on my laptop:
     4X 2.7GHz CPU's, 32 GB - multiple versions of SQL Server (2005-2019)
 2.  The lowest and highest possible numbers returned are whatever is allowable by a
     bigint. The function, however, returns no more than 531,441,000,000 rows (8100^3).
 3.  @Gap does not affect RN, RN will begin at @Row1 and increase by 1 until the last row
     unless its used in a subquery where a filter is applied to RN.
 4.  @Gap must be greater than 0 or the function will not return any rows.
 5.  Keep in mind that when @Row1 is 0 then the highest RN value (ROWNUMBER) will be the
     number of rows returned minus 1
 6.  If you only need is a sequential set beginning at 0 or 1 then, for best performance
     use the RN column. Use N1 and/or N2 when you need to begin your sequence at any
     number other than 0 or 1 or if you need a gap between your sequence of numbers.
 7.  Although @Gap is a bigint it must be a positive integer or the function will
     not return any rows.
 8.  The function will not return any rows when one of the following conditions are true:
       * any of the input parameters are NULL
       * @High is less than @Low
       * @Gap is not greater than 0
     To force the function to return all NULLs instead of not returning anything you can
     add the following code to the end of the query:

       UNION ALL
       SELECT NULL, NULL, NULL, NULL
       WHERE NOT (@High&@Low&@Gap&@Row1 IS NOT NULL AND @High >= @Low AND @Gap > 0)

     This code was excluded as it adds a ~5% performance penalty.
 9.  There is no performance penalty for sorting by RN ASC; there is a large performance
     penalty, however for sorting in descending order. If you need a descending sort the
     use OP in place of RN then sort by rn ASC.
 10. When setting the @Row1 to 0 and sorting by RN you will see that the 0 is added via
     MERGE JOIN concatination. Under the hood the function is essentially concatinating
     but, because it's using a MERGE JOIN operator instead of concatination the cost
     estimations are needlessly high. You can circumvent this problem by changing:
     ORDER BY core.rangeAB.RN to: ORDER BY ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

[Examples]:

-----------------------------------------------------------------------------------------
[Revision History]:
 Rev 00 - 20140518 - Initial Development - AJB
 Rev 05 - 20191122 - Developed this "core" version for open source distribution;
                     updated notes and did some final code clean-up
*****************************************************************************************/
RETURNS TABLE WITH SCHEMABINDING AS RETURN
WITH
L1(N) AS
(
  SELECT 1
  FROM (VALUES
   ($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),
   ($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),
   ($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),
   ($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),
   ($),($)) T(N) -- 90 values
),
L2(N)      AS (SELECT 1 FROM L1 a CROSS JOIN L1 b CROSS JOIN L1 c),
iTally(RN) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) FROM L2 a CROSS JOIN L2 b)
SELECT r.RN, r.OP, r.N1, r.N2
FROM
(
  SELECT
    RN = 0,
    OP = (@High-@Low)/@Gap,
    N1 = @Low,
    N2 = @Gap+@Low
  WHERE @Row1 = 0
  UNION ALL   -- (@High-@Low)/@Gap+1:
  SELECT TOP (ABS((ISNULL(@High,0)-ISNULL(@Low,0))/ISNULL(@Gap,0)+ISNULL(@Row1,1)))
    RN = i.RN,
    OP = (@High-@Low)/@Gap+(2*@Row1)-i.RN,
    N1 = (i.rn-@Row1)*@Gap+@Low,
    N2 = (i.rn-(@Row1-1))*@Gap+@Low
  FROM       iTally AS i
  ORDER BY   i.RN
) AS r
WHERE @High&@Low&@Gap&@Row1 IS NOT NULL AND @High >= @Low
AND   @Gap > 0;
GO

CREATE FUNCTION samd.ngrams8k
(
  @String VARCHAR(8000), -- Input string
  @N      INT            -- requested token size
)
/*****************************************************************************************
[Purpose]:
 A character-level N-Grams function that outputs a contiguous stream of @N-sized tokens
 based on an input string (@String). Accepts strings up to 8000 varchar characters long.
 For more information about N-Grams see: http://en.wikipedia.org/wiki/N-gram.

[Author]:
 Alan Burstein

[Compatibility]:
 SQL Server 2008+, Azure SQL Database

[Syntax]:
--===== Autonomous
 SELECT ng.Position, ng.Token
 FROM   samd.ngrams8k(@String,@N) AS ng;

--===== Against a table using APPLY
 SELECT      s.SomeID, ng.Position, ng.Token
 FROM        dbo.SomeTable                 AS s
 CROSS APPLY samd.ngrams8k(s.SomeValue,@N) AS ng;

[Parameters]:
 @String  = The input string to split into tokens.
 @N       = The size of each token returned.

[Returns]:
 Position = BIGINT; the position of the token in the input string
 token    = VARCHAR(8000); a @N-sized character-level N-Gram token

[Dependencies]:
 1. core.rangeAB (iTVF)

[Developer Notes]:
 1. ngrams8k is not case sensitive;

 2. Many functions that use ngrams8k will see a huge performance gain when the optimizer
    creates a parallel execution plan. One way to get a parallel query plan (if the
    optimizer does not choose one) is to use make_parallel by Adam Machanic which can be
    found here:
 sqlblog.com/blogs/adam_machanic/archive/2013/07/11/next-level-parallel-plan-porcing.aspx

3. When @N is less than 1 or greater than the datalength of the input string then no
    tokens (rows) are returned. If either @String or @N are NULL no rows are returned.
    This is a debatable topic but the thinking behind this decision is that: because you
    can't split 'xxx' into 4-grams, you can't split a NULL value into unigrams and you
    can't turn anything into NULL-grams, no rows should be returned.

    For people who would prefer that a NULL input forces the function to return a single
    NULL output you could add this code to the end of the function:

    UNION ALL
    SELECT 1, NULL
    WHERE NOT(@N > 0 AND @N <= DATALENGTH(@String)) OR (@N IS NULL OR @String IS NULL)

 4. ngrams8k is deterministic. For more about deterministic functions see:
    https://msdn.microsoft.com/en-us/library/ms178091.aspx

[Examples]:
--===== 1. Split the string, "abcd" into unigrams, bigrams and trigrams
 SELECT ng.Position, ng.Token FROM samd.ngrams8k('abcd',1) AS ng; -- unigrams (@N=1)
 SELECT ng.Position, ng.Token FROM samd.ngrams8k('abcd',2) AS ng; -- bigrams  (@N=2)
 SELECT ng.Position, ng.Token FROM samd.ngrams8k('abcd',3) AS ng; -- trigrams (@N=3)

[Revision History]:
------------------------------------------------------------------------------------------
 Rev 00 - 20140310 - Initial Development - Alan Burstein
 Rev 01 - 20150522 - Removed DQS N-Grams functionality, improved iTally logic. Also Added
                     conversion to bigint in the TOP logic to remove implicit conversion
                     to bigint - Alan Burstein
 Rev 05 - 20171228 - Small simplification; changed:
                (ABS(CONVERT(BIGINT,(DATALENGTH(ISNULL(@String,''))-(ISNULL(@N,1)-1)),0)))
                                           to:
                (ABS(CONVERT(BIGINT,(DATALENGTH(ISNULL(@String,''))+1-ISNULL(@N,1)),0)))
 Rev 06 - 20180612 - Using CHECKSUM(N) in the to convert N in the token output instead of
                     using (CAST N as int). CHECKSUM removes the need to convert to int.
 Rev 07 - 20180612 - re-designed to: Use core.rangeAB - Alan Burstein
*****************************************************************************************/
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
  Position   = r.RN,
  Token      = SUBSTRING(@String,CHECKSUM(r.RN),@N)
FROM  core.rangeAB(1,LEN(@String)+1-@N,1,1) AS r
WHERE @N > 0 AND @N <= LEN(@String);
GO

CREATE FUNCTION samd.patReplace8K
(
  @string  VARCHAR(8000),
  @pattern VARCHAR(50),
  @replace VARCHAR(20)
)
/*****************************************************************************************
[Purpose]:
 Given a string (@string), a pattern (@pattern), and a replacement character (@replace)
 patReplace8K will replace any character in @string that matches the @Pattern parameter
 with the character, @replace.

[Author]:
 Alan Burstein

[Compatibility]:
  SQL Server 2008+

[Syntax]:
--===== Basic Syntax Example
 SELECT pr.NewString
 FROM   samd.patReplace8K(@String,@Pattern,@Replace) AS pr;

[Developer Notes]:
 1. Required SQL Server 2008+
 2. @Pattern IS case sensitive but can be easily modified to make it case insensitive
 3. There is no need to include the "%" before and/or after your pattern since since we
    are evaluating each character individually
 4. Certain special characters, such as "$" and "%" need to be escaped with a "/"
    like so: [/$/%]

[Examples]:
--===== 1. Replace numeric characters with a "*"
 SELECT pr.NewString
 FROM  samd.patReplace8K('My phone number is 555-2211','[0-9]','*') AS pr;

[Revision History]:
 Rev 00 - 10/27/2014 Initial Development - Alan Burstein
 Rev 01 - 10/29/2014 Mar 2007 - Alan Burstein
        - Redesigned based on the dbo.STRIP_NUM_EE by Eirikur Eiriksson
          (see: http://www.sqlservercentral.com/Forums/Topic1585850-391-2.aspx)
        - change how the cte tally table is created
        - put the include/exclude logic in a CASE statement instead of a WHERE clause
        - Added Latin1_General_BIN Colation
        - Add code to use the pattern as a parameter.
 Rev 02 - 20141106
        - Added final performane enhancement (more cudo's to Eirikur Eiriksson)
        - Put 0 = PATINDEX filter logic into the WHERE clause
Rev 03  - 20150516
        - Updated to deal with special XML characters
Rev 04  - 20170320
        - changed @replace from char(1) to varchar(1) to address how spaces are handled
Rev 05  - Re-write using samd.NGrams
*****************************************************************************************/
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT newString =
  (
    SELECT   CASE WHEN @string = CAST('' AS VARCHAR(8000))   THEN CAST('' AS VARCHAR(8000))
                  WHEN @pattern+@replace+@string IS NOT NULL THEN
                    CASE WHEN PATINDEX(@pattern,token COLLATE Latin1_General_BIN)=0
                         THEN ng.token ELSE @replace         END END
    FROM     samd.NGrams8K(@string, 1) AS ng
    ORDER BY ng.position
    FOR XML PATH(''),TYPE
  ).value('text()[1]', 'VARCHAR(8000)');
GO

CREATE FUNCTION samd.Instr8k
(
  @string    VARCHAR(8000),
  @search    VARCHAR(8000),
  @position  INT,
  @occurance INT
)
/*****************************************************************************************
[Purpose]:
  Returns the position (ItemIndex) of the Nth(@occurance) occurrence of one string(@search) within
  another(@string). Similar to Oracle's PL/SQL INSTR funtion.
  https://www.techonthenet.com/oracle/functions/instr.php

[Author]:
 Alan Burstein

[Compatibility]:
 SQL Server 2008+

[Syntax]:
--===== Autonomous
 SELECT ins.ItemIndex, ins.ItemLength, ins.ItemCount
 FROM   samd.Instr8k(@string,@search,@position,@occurance) AS ins;

--===== Against a table using APPLY
 SELECT      s.SomeID, ins.ItemIndex, ins.ItemLength, ins.ItemCount
 FROM        dbo.SomeTable                 AS s
 CROSS APPLY samd.Instr8k(s.string,@search,@position,@occurance) AS ins

[Parameters]:
  @string    = VARCHAR(8000); Input sting to evaluate
  @search    = VARCHAR(8000); Token to search for inside of @string
  @position  = INT; Where to begin searching for @search; identical to the third
                     parameter in SQL Server CHARINDEX [, start_location]
  @occurance = INT; Represents the Nth instance of the search string (@search)

[Returns]:
  ItemIndex  = Position of the Nth (@occurance) instance of @search inside @string
  ItemLength = Length of @search (in case you need it, no need to re-evaluate the string)
  ItemCount  = Number of times @search appears inside @string

[Dependencies]:
 1. samd.ngrams8k
   1.1. dbo.rangeAB (iTVF)
 2. samd.substringCount8K_lazy

[Developer Notes]:
 1. samd.Instr8k does not treat the input strings (@string and @search) as case sensitive.
 2. Don't use instr8k for "SubstringBetween" functionality; for better performance use
    samd.SubstringBetween8k instead.
 3. The @position parameter is the key benefit of this function when dealing with long
    strings where the search item is towards the back of the string. For example, take a
    5000 character string where, what you are looking for is always *at least* 3000
    characters deep. Setting @position to 3000 will dramatically improve performance.
 4. Unlike Oracle's PL/SQL INSTR function, Instr8k does not accept numbers less than 1.

[Examples]:

[Revision History]:
------------------------------------------------------------------------------------------
 Rev 00 - 20191112 - Initial Development - Alan Burstein

*****************************************************************************************/
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
  ItemIndex  = ISNULL(MAX(ISNULL(instr.Position,1)+(a.Pos-1)),0),
  ItemLength = ISNULL(MAX(LEN(@search)),LEN(@search)),
  ItemCount  = ISNULL(MAX(items.SubstringCount),0)
FROM        (VALUES(ISNULL(@position,1),LEN(@search)))   AS a(Pos,SrchLn)
CROSS APPLY (VALUES(SUBSTRING(@string,a.Pos,8000)))      AS f(String)
CROSS APPLY samd.substringCount8K_lazy(f.string,@search) AS items
CROSS APPLY
(
  SELECT TOP (@occurance) RN = ROW_NUMBER() OVER (ORDER BY ng.position), ng.position
  FROM   samd.ngrams8k(f.string,a.SrchLn) AS ng
  WHERE  ng.token = @search
  ORDER BY RN
) AS instr
WHERE a.Pos > 0
AND   @occurance <= items.SubstringCount
AND   instr.RN = @occurance;
GO

CREATE FUNCTION samd.substringCount8K_lazy
(
  @string       varchar(8000),
  @searchstring varchar(1000)
)
/*****************************************************************************************
[Purpose]:
 Scans the input string (@string) and counts how many times the search character
 (@searchChar) appears. This function is Based on Itzik Ben-Gans cte numbers table logic
[Compatibility]:
 SQL Server 2008+
 Uses TABLE VALUES constructor (not available pre-2008)

[Author]: Alan Burstein

[Syntax]:
--===== Autonomous
 SELECT f.substringCount
 FROM   samd.substringCount8K_lazy(@string,@searchString) AS f;

--===== Against a table using APPLY
 SELECT      f.substringCount
 FROM        dbo.someTable AS t
 CROSS APPLY samd.substringCount8K_lazy(t.col, @searchString) AS f;

Parameters:
  @string       = VARCHAR(8000); input string to analyze
  @searchString = VARCHAR(1000); substring to search for

[Returns]:
 Inline table valued function returns -
 substringCount = int; Number of times that @searchChar appears in @string

[Developer Notes]:
 1. substringCount8K_lazy does NOT take overlapping values into consideration. For
    example, this query will return a 1 but the correct result is 2:

      SELECT substringCount FROM samd.substringCount8K_lazy('xxx','xx')

    When overlapping values are a possibility or concern then use substringCountAdvanced8k

 2. substringCount8K_lazy is what is referred to as an "inline" scalar UDF." Technically
    it's aninline table valued function (iTVF) but performs the same task as a scalar
    valued user defined function (UDF); the difference is that it requires the APPLY table
    operator to accept column values as a parameter. For more about "inline" scalar UDFs
    see thisarticle by SQL MVP Jeff Moden:
      http://www.sqlservercentral.com/articles/T-SQL/91724/
    and for more about how to use APPLY see the this article by SQL MVP Paul White:
      http://www.sqlservercentral.com/articles/APPLY/69953/.

    Note the above syntax example and usage examples below to better understand how to
    use the function. Although the function is slightly more complicated to use than a
    scalar UDF it will yield notably better performance for many reasons. For example,
    unlike a scalar UDFs or multi-line table valued functions, the inline scalar UDF does
    not restrict the query optimizer's ability generate a parallel query execution plan.

 3. substringCount8K_lazy returns NULL when either input parameter is NULL and returns 0
    when either input parameter is blank.

 4. substringCount8K_lazy does not treat parameters as cases senstitive

 5. substringCount8K_lazy is deterministic. For more deterministic functions see:
    https://msdn.microsoft.com/en-us/library/ms178091.aspx

[Examples]:
--===== 1. How many times does the substring "abc" appear?
 SELECT f.* FROM samd.substringCount8k_lazy('abc123xxxabc','abc') AS f;

--===== 2. Return records from a table where the substring "ab" appears more than once
 DECLARE @table TABLE (string varchar(8000));
 DECLARE @searchString varchar(1000) = 'ab';
 INSERT  @table VALUES ('abcabc'),('abcd'),('bababab'),('baba'),(NULL);

 SELECT      searchString = @searchString, t.string, f.substringCount
 FROM        @table                                  AS t
 CROSS APPLY samd.substringCount8k_lazy(string,'ab') AS f
 WHERE       f.substringCount > 1;

-----------------------------------------------------------------------------------------
[Revision History]:
 Rev 00 - 20180625 - Initial Development - Alan Burstein
 Rev 01 - 20190102 - Added logic to better handle @searchstring = char(32) - Alan Burstein
*****************************************************************************************/
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT      substringCount = (LEN(v.s)-LEN(REPLACE(v.s,v.st,'')))/d.l
FROM        (VALUES(DATALENGTH(@searchstring)))                      AS d(l)
CROSS APPLY (VALUES(@string,CASE WHEN d.l>0 THEN @searchstring END)) AS v(s,st);
GO
```

Title: PLSQL to TSQL - REGEXP

Body:
Im trying to convert a script from PLSQL to TSQL and am stuff with a couple of lines

table(cast(multiset(select level from dual connect by level <= len (regexp_replace(t.image, '[^**]+'))/2) as sys.OdciNumberList)) levels

where substr(REGEXP_SUBSTR (t.image,  '[^**]+',1, levels.column_value),1,instr( REGEXP_SUBSTR (t.image,  '[^**]+',1, levels.column_value),'=',1) -1)

IMAGE

Any help would be great.

Chris

Body:
For a better answer it would be good to include some sample input and desired results. Especially when addressing a different version of SQL. Perhaps including a PL/SQL tag would help find someone who understands PL/SQL and T-SQL. It would also be helpful to include DDL, specifically the datatype for "Level". Again, I say this not to be critical but rather guide you towards getting better answers here.

All That said, you can accomplish what you are trying to do in T-SQL leveraging a tally table, an N-Grams function and a couple other functions which I are included at the end of this post.

regexp_replace

To replace or remove characters that match a pattern in t-SQL you can use patreplace8k. Here's an example of how to use it to replace numbers with *'s:

SELECT pr.NewString
FROM  samd.patReplace8K('My phone number is 555-2211','[0-9]','*') AS pr;

Returns: My phone number is -*

regexp_subsr

Here's an example of how to extract all phone numbers from a string:

DECLARE
  @string  VARCHAR(8000)  = 'Call me later at 222-3333 or tomorrow at 312.555.2222,
                             (313)555-6789, or at 1+800-555-4444 before noon. Thanks!',
  @pattern VARCHAR(50)    = '%[^0-9()+.-]%';

-- EXTRACTOR
SELECT ItemNumber = ROW_NUMBER() OVER (ORDER BY f.position),
       ItemIndex  = f.position,
       ItemLength = itemLen.l,
       Item       = SUBSTRING(f.token, 1, itemLen.l)
FROM
(
 SELECT ng.position, SUBSTRING(@string,ng.position,DATALENGTH(@string))
 FROM   samd.NGrams8k(@string, 1) AS ng
 WHERE  PATINDEX(@pattern, ng.token) <  --<< this token does NOT match the pattern
        ABS(SIGN(ng.position-1)-1) +    --<< are you the first row?  OR
        PATINDEX(@pattern,SUBSTRING(@string,ng.position-1,1)) --<< always 0 for 1st row
) AS f(position, token)
CROSS APPLY (VALUES(ISNULL(NULLIF(PATINDEX(@pattern,f.token),0), --CROSS APPLY (VALUES(ISNULL(NULLIF(PATINDEX('%'+@pattern+'%',f.token),0),
  DATALENGTH(@string)+2-f.position)-1)) AS itemLen(l)
WHERE    itemLen.L > 6 -- this filter is more harmful to the extractor than the splitter
ORDER BY ItemNumber;

T-SQL INSTR Function

I included a T-SQL version of Oracles INSTR function at the end of this post. Note these examples:

DECLARE
  @string    VARCHAR(8000) = 'AABBCC-AA123-AAXYZPDQ-AA-54321',
  @search    VARCHAR(8000) = '-AA',
  @position  INT           = 1,
  @occurance INT           = 2;

-- 1.1. Get me the 2nd @occurance "-AA" in @string beginning at @position 1
SELECT f.* FROM samd.instr8k(@string,@search,@position,@occurance) AS f;

-- 1.2. Retreive everything *BEFORE* the second instance of "-AA"
SELECT
  ItemIndex = f.ItemIndex,
  Item      = SUBSTRING(@string,1,f.itemindex-1)
FROM samd.instr8k(@string,@search,@position,@occurance) AS f;

-- 1.3. Retreive everything *AFTER* the second instance of "-AA"
SELECT
  ItemIndex = MAX(f.ItemIndex),
  Item      = MAX(SUBSTRING(@string,f.itemindex+f.itemLength,8000))
FROM   samd.instr8k(@string,@search,@position,@occurance) AS f;

regexp_replace (ADVANCED)

Here's a more complex example, leveraging ngrams8k to replace phone numbers with the text "REMOVED"

DECLARE
  @string  VARCHAR(8000)  = 'Call me later at 222-3333 or tomorrow at 312.555.2222, (313)555-6789, or at 1+800-555-4444 before noon. Thanks!',
  @pattern VARCHAR(50)    = '%[0-9()+.-]%';

SELECT NewString = (
  SELECT IIF(IsMatch=1 AND patSplit.item LIKE '%[0-9][0-9][0-9]%','<REMOVED>', patSplit.item)
  FROM
  (
    SELECT 1, i.Idx, SUBSTRING(@string,1,i.Idx), CAST(0 AS BIT)
    FROM   (VALUES(PATINDEX(@pattern,@string)-1)) AS i(Idx) --FROM   (VALUES(PATINDEX('%'+@pattern+'%',@string)-1)) AS i(Idx)
    WHERE  SUBSTRING(@string,1,1) NOT LIKE @pattern
    UNION ALL
    SELECT r.RN,
           itemLength = LEAD(r.RN,1,DATALENGTH(@string)+1) OVER (ORDER BY r.RN)-r.RN,
           item       = SUBSTRING(@string,r.RN,
                        LEAD(r.RN,1,DATALENGTH(@string)+1) OVER (ORDER BY r.RN)-r.RN),
           isMatch    = ABS(t.p-2+1)
    FROM   core.rangeAB(1,DATALENGTH(@string),1,1) AS r
    CROSS APPLY (VALUES (
      CAST(PATINDEX(@pattern,SUBSTRING(@string,r.RN,1))   AS BIT),
      CAST(PATINDEX(@pattern,SUBSTRING(@string,r.RN-1,1)) AS BIT),
      SUBSTRING(@string,r.RN,r.Op+1))) AS t(c,p,s)
    WHERE t.c^t.p = 1
  ) AS patSplit(ItemIndex, ItemLength, Item, IsMatch)
  FOR XML PATH(''), TYPE).value('.','varchar(8000)');

Returns:

Call me later at  or tomorrow at , , or at  before noon. Thanks!

CREATE FUNCTION core.rangeAB
(
  @Low  BIGINT, -- (start) Lowest  number in the set
  @High BIGINT, -- (stop)  Highest number in the set
  @Gap  BIGINT, -- (step)  Difference between each number in the set
  @Row1 BIT     -- Base: 0 or 1; should RN begin with 0 or 1?
)
/****************************************************************************************
[Purpose]:
 Creates a lazy, in-memory, forward-ordered sequence of up to 531,441,000,000 integers
 starting with @Low and ending with @High (inclusive). RangeAB is a pure, 100% set-based
 alternative to solving SQL problems using iterative methods such as loops, cursors and
 recursive CTEs. RangeAB is based on Itzik Ben-Gan's getnums function for producing a
 sequence of integers and uses logic from Jeff Moden's fnTally function which includes a
 parameter for determining if the "row-number" (RN) should begin with 0 or 1.

 I wanted to use the name "Range" because it functions and performs almost identically to
 the Range function built into Python and Clojure. RANGE is a reserved SQL keyword so I
 went with "RangeAB". Functions/Algorithms developed using rangeAB can be easilty ported
 over to Python, Clojure or any other programming language that leverages a lazy sequence.
 The two major differences between RangeAB and the Python/Clojure versions are:
   1. RangeAB is *Inclusive* where the other two are *Exclusive". range(0,3) in Python and
      Clojure return [0 1 2], core.rangeAB(0,3) returns [0 1 2 3].
   2. RangeAB has a fourth Parameter (@Row1) to determine if RN should begin with 0 or 1.

[Author]:
 Alan Burstein

[Compatibility]:
 SQL Server 2008+

[Syntax]:
 SELECT r.RN, r.OP, r.N1, r.N2
 FROM   core.rangeAB(@Low,@High,@Gap,@Row1) AS r;

[Parameters]:
 @Low  = BIGINT; represents the lowest  value for N1.
 @High = BIGINT; represents the highest value for N1.
 @Gap  = BIGINT; represents how much N1 and N2 will increase each row. @Gap is also the
                 difference between N1 and N2.
 @Row1 = BIT;    represents the base (first) value of RN. When @Row1 = 0, RN begins with 0,
                 when @row = 1 then RN begins with 1.

[Returns]:
 Inline Table Valued Function returns:
 RN = BIGINT; a row number that works just like T-SQL ROW_NUMBER() except that it can
      start at 0 or 1 which is dictated by @Row1. If you need the numbers:
      (0 or 1) through @High, then use RN as your "N" value, ((@Row1=0 for 0, @Row1=1),
      otherwise use N1.
 OP = BIGINT; returns the "finite opposite" of RN. When RN begins with 0 the first number
      in the set will be 0 for RN, the last number in will be 0 for OP. When returning the
      numbers 1 to 10, 1 to 10 is retrurned in ascending order for RN and in descending
      order for OP.
      Given the Numbers 1 to 3, 3 is the opposite of 1, 2 the opposite of 2, and 1 is the
      opposite of 3. Given the numbers -1 to 2, the opposite of -1 is 2, the opposite of 0
      is 1, and the opposite of 1 is 0.
      The best practie is to only use OP when @Gap > 1; use core.O instead. Doing so will
      improve performance by 1-2% (not huge but every little bit counts)
 N1 = BIGINT; This is the "N" in your tally table/numbers function. this is your *Lazy*
      sequence of numbers starting at @Low and incrementing by @Gap until the next number
      in the sequence is greater than @High.
 N2 = BIGINT; a lazy sequence of numbers starting @Low+@Gap and incrementing by @Gap. N2
      will always be greater than N1 by @Gap. N2 can also be thought of as:
      LEAD(N1,1,N1+@Gap) OVER (ORDER BY RN)

[Dependencies]:
 N/A

[Developer Notes]:
 1.  core.rangeAB returns one billion rows in exactly 90 seconds on my laptop:
     4X 2.7GHz CPU's, 32 GB - multiple versions of SQL Server (2005-2019)
 2.  The lowest and highest possible numbers returned are whatever is allowable by a
     bigint. The function, however, returns no more than 531,441,000,000 rows (8100^3).
 3.  @Gap does not affect RN, RN will begin at @Row1 and increase by 1 until the last row
     unless its used in a subquery where a filter is applied to RN.
 4.  @Gap must be greater than 0 or the function will not return any rows.
 5.  Keep in mind that when @Row1 is 0 then the highest RN value (ROWNUMBER) will be the
     number of rows returned minus 1
 6.  If you only need is a sequential set beginning at 0 or 1 then, for best performance
     use the RN column. Use N1 and/or N2 when you need to begin your sequence at any
     number other than 0 or 1 or if you need a gap between your sequence of numbers.
 7.  Although @Gap is a bigint it must be a positive integer or the function will
     not return any rows.
 8.  The function will not return any rows when one of the following conditions are true:
       * any of the input parameters are NULL
       * @High is less than @Low
       * @Gap is not greater than 0
     To force the function to return all NULLs instead of not returning anything you can
     add the following code to the end of the query:

       UNION ALL
       SELECT NULL, NULL, NULL, NULL
       WHERE NOT (@High&@Low&@Gap&@Row1 IS NOT NULL AND @High >= @Low AND @Gap > 0)

     This code was excluded as it adds a ~5% performance penalty.
 9.  There is no performance penalty for sorting by RN ASC; there is a large performance
     penalty, however for sorting in descending order. If you need a descending sort the
     use OP in place of RN then sort by rn ASC.
 10. When setting the @Row1 to 0 and sorting by RN you will see that the 0 is added via
     MERGE JOIN concatination. Under the hood the function is essentially concatinating
     but, because it's using a MERGE JOIN operator instead of concatination the cost
     estimations are needlessly high. You can circumvent this problem by changing:
     ORDER BY core.rangeAB.RN to: ORDER BY ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

[Examples]:

-----------------------------------------------------------------------------------------
[Revision History]:
 Rev 00 - 20140518 - Initial Development - AJB
 Rev 05 - 20191122 - Developed this "core" version for open source distribution;
                     updated notes and did some final code clean-up
*****************************************************************************************/
RETURNS TABLE WITH SCHEMABINDING AS RETURN
WITH
L1(N) AS
(
  SELECT 1
  FROM (VALUES
   ($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),
   ($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),
   ($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),
   ($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),($),
   ($),($)) T(N) -- 90 values
),
L2(N)      AS (SELECT 1 FROM L1 a CROSS JOIN L1 b CROSS JOIN L1 c),
iTally(RN) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) FROM L2 a CROSS JOIN L2 b)
SELECT r.RN, r.OP, r.N1, r.N2
FROM
(
  SELECT
    RN = 0,
    OP = (@High-@Low)/@Gap,
    N1 = @Low,
    N2 = @Gap+@Low
  WHERE @Row1 = 0
  UNION ALL   -- (@High-@Low)/@Gap+1:
  SELECT TOP (ABS((ISNULL(@High,0)-ISNULL(@Low,0))/ISNULL(@Gap,0)+ISNULL(@Row1,1)))
    RN = i.RN,
    OP = (@High-@Low)/@Gap+(2*@Row1)-i.RN,
    N1 = (i.rn-@Row1)*@Gap+@Low,
    N2 = (i.rn-(@Row1-1))*@Gap+@Low
  FROM       iTally AS i
  ORDER BY   i.RN
) AS r
WHERE @High&@Low&@Gap&@Row1 IS NOT NULL AND @High >= @Low
AND   @Gap > 0;
GO

CREATE FUNCTION samd.ngrams8k
(
  @String VARCHAR(8000), -- Input string
  @N      INT            -- requested token size
)
/*****************************************************************************************
[Purpose]:
 A character-level N-Grams function that outputs a contiguous stream of @N-sized tokens
 based on an input string (@String). Accepts strings up to 8000 varchar characters long.
 For more information about N-Grams see: http://en.wikipedia.org/wiki/N-gram.

[Author]:
 Alan Burstein

[Compatibility]:
 SQL Server 2008+, Azure SQL Database

[Syntax]:
--===== Autonomous
 SELECT ng.Position, ng.Token
 FROM   samd.ngrams8k(@String,@N) AS ng;

--===== Against a table using APPLY
 SELECT      s.SomeID, ng.Position, ng.Token
 FROM        dbo.SomeTable                 AS s
 CROSS APPLY samd.ngrams8k(s.SomeValue,@N) AS ng;

[Parameters]:
 @String  = The input string to split into tokens.
 @N       = The size of each token returned.

[Returns]:
 Position = BIGINT; the position of the token in the input string
 token    = VARCHAR(8000); a @N-sized character-level N-Gram token

[Dependencies]:
 1. core.rangeAB (iTVF)

[Developer Notes]:
 1. ngrams8k is not case sensitive;

 2. Many functions that use ngrams8k will see a huge performance gain when the optimizer
    creates a parallel execution plan. One way to get a parallel query plan (if the
    optimizer does not choose one) is to use make_parallel by Adam Machanic which can be
    found here:
 sqlblog.com/blogs/adam_machanic/archive/2013/07/11/next-level-parallel-plan-porcing.aspx

3. When @N is less than 1 or greater than the datalength of the input string then no
    tokens (rows) are returned. If either @String or @N are NULL no rows are returned.
    This is a debatable topic but the thinking behind this decision is that: because you
    can't split 'xxx' into 4-grams, you can't split a NULL value into unigrams and you
    can't turn anything into NULL-grams, no rows should be returned.

    For people who would prefer that a NULL input forces the function to return a single
    NULL output you could add this code to the end of the function:

    UNION ALL
    SELECT 1, NULL
    WHERE NOT(@N > 0 AND @N <= DATALENGTH(@String)) OR (@N IS NULL OR @String IS NULL)

 4. ngrams8k is deterministic. For more about deterministic functions see:
    https://msdn.microsoft.com/en-us/library/ms178091.aspx

[Examples]:
--===== 1. Split the string, "abcd" into unigrams, bigrams and trigrams
 SELECT ng.Position, ng.Token FROM samd.ngrams8k('abcd',1) AS ng; -- unigrams (@N=1)
 SELECT ng.Position, ng.Token FROM samd.ngrams8k('abcd',2) AS ng; -- bigrams  (@N=2)
 SELECT ng.Position, ng.Token FROM samd.ngrams8k('abcd',3) AS ng; -- trigrams (@N=3)

[Revision History]:
------------------------------------------------------------------------------------------
 Rev 00 - 20140310 - Initial Development - Alan Burstein
 Rev 01 - 20150522 - Removed DQS N-Grams functionality, improved iTally logic. Also Added
                     conversion to bigint in the TOP logic to remove implicit conversion
                     to bigint - Alan Burstein
 Rev 05 - 20171228 - Small simplification; changed:
                (ABS(CONVERT(BIGINT,(DATALENGTH(ISNULL(@String,''))-(ISNULL(@N,1)-1)),0)))
                                           to:
                (ABS(CONVERT(BIGINT,(DATALENGTH(ISNULL(@String,''))+1-ISNULL(@N,1)),0)))
 Rev 06 - 20180612 - Using CHECKSUM(N) in the to convert N in the token output instead of
                     using (CAST N as int). CHECKSUM removes the need to convert to int.
 Rev 07 - 20180612 - re-designed to: Use core.rangeAB - Alan Burstein
*****************************************************************************************/
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
  Position   = r.RN,
  Token      = SUBSTRING(@String,CHECKSUM(r.RN),@N)
FROM  core.rangeAB(1,LEN(@String)+1-@N,1,1) AS r
WHERE @N > 0 AND @N <= LEN(@String);
GO

CREATE FUNCTION samd.patReplace8K
(
  @string  VARCHAR(8000),
  @pattern VARCHAR(50),
  @replace VARCHAR(20)
)
/*****************************************************************************************
[Purpose]:
 Given a string (@string), a pattern (@pattern), and a replacement character (@replace)
 patReplace8K will replace any character in @string that matches the @Pattern parameter
 with the character, @replace.

[Author]:
 Alan Burstein

[Compatibility]:
  SQL Server 2008+

[Syntax]:
--===== Basic Syntax Example
 SELECT pr.NewString
 FROM   samd.patReplace8K(@String,@Pattern,@Replace) AS pr;

[Developer Notes]:
 1. Required SQL Server 2008+
 2. @Pattern IS case sensitive but can be easily modified to make it case insensitive
 3. There is no need to include the "%" before and/or after your pattern since since we
    are evaluating each character individually
 4. Certain special characters, such as "$" and "%" need to be escaped with a "/"
    like so: [/$/%]

[Examples]:
--===== 1. Replace numeric characters with a "*"
 SELECT pr.NewString
 FROM  samd.patReplace8K('My phone number is 555-2211','[0-9]','*') AS pr;

[Revision History]:
 Rev 00 - 10/27/2014 Initial Development - Alan Burstein
 Rev 01 - 10/29/2014 Mar 2007 - Alan Burstein
        - Redesigned based on the dbo.STRIP_NUM_EE by Eirikur Eiriksson
          (see: http://www.sqlservercentral.com/Forums/Topic1585850-391-2.aspx)
        - change how the cte tally table is created
        - put the include/exclude logic in a CASE statement instead of a WHERE clause
        - Added Latin1_General_BIN Colation
        - Add code to use the pattern as a parameter.
 Rev 02 - 20141106
        - Added final performane enhancement (more cudo's to Eirikur Eiriksson)
        - Put 0 = PATINDEX filter logic into the WHERE clause
Rev 03  - 20150516
        - Updated to deal with special XML characters
Rev 04  - 20170320
        - changed @replace from char(1) to varchar(1) to address how spaces are handled
Rev 05  - Re-write using samd.NGrams
*****************************************************************************************/
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT newString =
  (
    SELECT   CASE WHEN @string = CAST('' AS VARCHAR(8000))   THEN CAST('' AS VARCHAR(8000))
                  WHEN @pattern+@replace+@string IS NOT NULL THEN
                    CASE WHEN PATINDEX(@pattern,token COLLATE Latin1_General_BIN)=0
                         THEN ng.token ELSE @replace         END END
    FROM     samd.NGrams8K(@string, 1) AS ng
    ORDER BY ng.position
    FOR XML PATH(''),TYPE
  ).value('text()[1]', 'VARCHAR(8000)');
GO

CREATE FUNCTION samd.Instr8k
(
  @string    VARCHAR(8000),
  @search    VARCHAR(8000),
  @position  INT,
  @occurance INT
)
/*****************************************************************************************
[Purpose]:
  Returns the position (ItemIndex) of the Nth(@occurance) occurrence of one string(@search) within
  another(@string). Similar to Oracle's PL/SQL INSTR funtion.
  https://www.techonthenet.com/oracle/functions/instr.php

[Author]:
 Alan Burstein

[Compatibility]:
 SQL Server 2008+

[Syntax]:
--===== Autonomous
 SELECT ins.ItemIndex, ins.ItemLength, ins.ItemCount
 FROM   samd.Instr8k(@string,@search,@position,@occurance) AS ins;

--===== Against a table using APPLY
 SELECT      s.SomeID, ins.ItemIndex, ins.ItemLength, ins.ItemCount
 FROM        dbo.SomeTable                 AS s
 CROSS APPLY samd.Instr8k(s.string,@search,@position,@occurance) AS ins

[Parameters]:
  @string    = VARCHAR(8000); Input sting to evaluate
  @search    = VARCHAR(8000); Token to search for inside of @string
  @position  = INT; Where to begin searching for @search; identical to the third
                     parameter in SQL Server CHARINDEX [, start_location]
  @occurance = INT; Represents the Nth instance of the search string (@search)

[Returns]:
  ItemIndex  = Position of the Nth (@occurance) instance of @search inside @string
  ItemLength = Length of @search (in case you need it, no need to re-evaluate the string)
  ItemCount  = Number of times @search appears inside @string

[Dependencies]:
 1. samd.ngrams8k
   1.1. dbo.rangeAB (iTVF)
 2. samd.substringCount8K_lazy

[Developer Notes]:
 1. samd.Instr8k does not treat the input strings (@string and @search) as case sensitive.
 2. Don't use instr8k for "SubstringBetween" functionality; for better performance use
    samd.SubstringBetween8k instead.
 3. The @position parameter is the key benefit of this function when dealing with long
    strings where the search item is towards the back of the string. For example, take a
    5000 character string where, what you are looking for is always *at least* 3000
    characters deep. Setting @position to 3000 will dramatically improve performance.
 4. Unlike Oracle's PL/SQL INSTR function, Instr8k does not accept numbers less than 1.

[Examples]:

[Revision History]:
------------------------------------------------------------------------------------------
 Rev 00 - 20191112 - Initial Development - Alan Burstein

*****************************************************************************************/
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT
  ItemIndex  = ISNULL(MAX(ISNULL(instr.Position,1)+(a.Pos-1)),0),
  ItemLength = ISNULL(MAX(LEN(@search)),LEN(@search)),
  ItemCount  = ISNULL(MAX(items.SubstringCount),0)
FROM        (VALUES(ISNULL(@position,1),LEN(@search)))   AS a(Pos,SrchLn)
CROSS APPLY (VALUES(SUBSTRING(@string,a.Pos,8000)))      AS f(String)
CROSS APPLY samd.substringCount8K_lazy(f.string,@search) AS items
CROSS APPLY
(
  SELECT TOP (@occurance) RN = ROW_NUMBER() OVER (ORDER BY ng.position), ng.position
  FROM   samd.ngrams8k(f.string,a.SrchLn) AS ng
  WHERE  ng.token = @search
  ORDER BY RN
) AS instr
WHERE a.Pos > 0
AND   @occurance <= items.SubstringCount
AND   instr.RN = @occurance;
GO

CREATE FUNCTION samd.substringCount8K_lazy
(
  @string       varchar(8000),
  @searchstring varchar(1000)
)
/*****************************************************************************************
[Purpose]:
 Scans the input string (@string) and counts how many times the search character
 (@searchChar) appears. This function is Based on Itzik Ben-Gans cte numbers table logic
[Compatibility]:
 SQL Server 2008+
 Uses TABLE VALUES constructor (not available pre-2008)

[Author]: Alan Burstein

[Syntax]:
--===== Autonomous
 SELECT f.substringCount
 FROM   samd.substringCount8K_lazy(@string,@searchString) AS f;

--===== Against a table using APPLY
 SELECT      f.substringCount
 FROM        dbo.someTable AS t
 CROSS APPLY samd.substringCount8K_lazy(t.col, @searchString) AS f;

Parameters:
  @string       = VARCHAR(8000); input string to analyze
  @searchString = VARCHAR(1000); substring to search for

[Returns]:
 Inline table valued function returns -
 substringCount = int; Number of times that @searchChar appears in @string

[Developer Notes]:
 1. substringCount8K_lazy does NOT take overlapping values into consideration. For
    example, this query will return a 1 but the correct result is 2:

      SELECT substringCount FROM samd.substringCount8K_lazy('xxx','xx')

    When overlapping values are a possibility or concern then use substringCountAdvanced8k

 2. substringCount8K_lazy is what is referred to as an "inline" scalar UDF." Technically
    it's aninline table valued function (iTVF) but performs the same task as a scalar
    valued user defined function (UDF); the difference is that it requires the APPLY table
    operator to accept column values as a parameter. For more about "inline" scalar UDFs
    see thisarticle by SQL MVP Jeff Moden:
      http://www.sqlservercentral.com/articles/T-SQL/91724/
    and for more about how to use APPLY see the this article by SQL MVP Paul White:
      http://www.sqlservercentral.com/articles/APPLY/69953/.

    Note the above syntax example and usage examples below to better understand how to
    use the function. Although the function is slightly more complicated to use than a
    scalar UDF it will yield notably better performance for many reasons. For example,
    unlike a scalar UDFs or multi-line table valued functions, the inline scalar UDF does
    not restrict the query optimizer's ability generate a parallel query execution plan.

 3. substringCount8K_lazy returns NULL when either input parameter is NULL and returns 0
    when either input parameter is blank.

 4. substringCount8K_lazy does not treat parameters as cases senstitive

 5. substringCount8K_lazy is deterministic. For more deterministic functions see:
    https://msdn.microsoft.com/en-us/library/ms178091.aspx

[Examples]:
--===== 1. How many times does the substring "abc" appear?
 SELECT f.* FROM samd.substringCount8k_lazy('abc123xxxabc','abc') AS f;

--===== 2. Return records from a table where the substring "ab" appears more than once
 DECLARE @table TABLE (string varchar(8000));
 DECLARE @searchString varchar(1000) = 'ab';
 INSERT  @table VALUES ('abcabc'),('abcd'),('bababab'),('baba'),(NULL);

 SELECT      searchString = @searchString, t.string, f.substringCount
 FROM        @table                                  AS t
 CROSS APPLY samd.substringCount8k_lazy(string,'ab') AS f
 WHERE       f.substringCount > 1;

-----------------------------------------------------------------------------------------
[Revision History]:
 Rev 00 - 20180625 - Initial Development - Alan Burstein
 Rev 01 - 20190102 - Added logic to better handle @searchstring = char(32) - Alan Burstein
*****************************************************************************************/
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT      substringCount = (LEN(v.s)-LEN(REPLACE(v.s,v.st,'')))/d.l
FROM        (VALUES(DATALENGTH(@searchstring)))                      AS d(l)
CROSS APPLY (VALUES(@string,CASE WHEN d.l>0 THEN @searchstring END)) AS v(s,st);
GO


问:
Laravel, rotating images expects parameter to be a valid path
When I upload images to the database Laravel rotates them, so I found
Laravel is rotating the image when uploaded, then after implementing it I get this error exif_read_data() expects parameter 1 to be a valid path, array given. How can I fix this?

Controller

```<code> if($request->hasFile('files')){

        $store_file = [];

        $exif = exif_read_data($request->file('files'));
        if(!empty($exif['Orientation'])) {
            switch($exif['Orientation']) {
                case 8:
                    $image = imagerotate($image,90,0);
                    break;
                case 3:
                    $image = imagerotate($image,180,0);
                    break;
                case 6:
                    $image = imagerotate($image,-90,0);
                    break;
            }
        }

       // $files = $request->file('files');
        foreach ($exif as $file) {
            $path = $file->storeAs('public/photos');

            $store_file[] = [

                'product_id' => $product->id,
                'filename' =>  $path
            ];
        }
</code>```

答:
As ther error mentiened exif_read_data() expects parameter 1 to be a valid path, array given.

You must provide image path from storage to exif_read_data() function which mean you need to loop through $request->file('files') and get the storage path via

```<code>$path = $file->store('public/photos');
</code>```

Then

```<code>exif_read_data($path);
</code>```

Fix

```<code>if($request->hasFile('files')){

    $store_file = [];

    foreach ($request->file('files') as $file) {

        $fileName = uniqid() . time() . '.' . $file->getClientOriginalExtension();

        $file->storeAs('public/photos', $fileName);

        $filePath = $file->getPathName();

        $exif = @exif_read_data($filePath);

        if(isset($exif['Orientation'])) {

            switch($exif['Orientation']) {
                case 8:
                    $deg = 90;
                    break;
                case 3:
                    $deg = 180;
                    break;
                case 6:
                    $deg = -90;
                    break;
            }

            if (isset($deg)) {

                $fullQualifiedFilePath = storage_path('app/public/photos/' . $fileName);

                if ($file->getClientOriginalExtension() == "png") {
                    $img_new = imagecreatefrompng($fullQualifiedFilePath);
                    $img_new = imagerotate($img_new, $deg, 0);

                    imagepng($img_new, $fullQualifiedFilePath);
                } else {
                    $img_new = imagecreatefromjpeg($fullQualifiedFilePath);
                    $img_new = imagerotate($img_new, $deg, 0);

                    imagejpeg($img_new, $fullQualifiedFilePath, 80);
                }
           }
       }

        $store_file[] = [

            'product_id' => $product->id,
            'filename' =>  'public/photos/' . $fileName
        ];
    }

    ProductsPhoto::insert($store_file);
}
</code>```

Reference How to do a rotation of an image with PHP (Laravel)

Body:
When I upload images to the database Laravel rotates them, so I found
Laravel is rotating the image when uploaded, then after implementing it I get this error exif_read_data() expects parameter 1 to be a valid path, array given. How can I fix this?

Controller

 if($request->hasFile('files')){

        $store_file = [];

        $exif = exif_read_data($request->file('files'));
        if(!empty($exif['Orientation'])) {
            switch($exif['Orientation']) {
                case 8:
                    $image = imagerotate($image,90,0);
                    break;
                case 3:
                    $image = imagerotate($image,180,0);
                    break;
                case 6:
                    $image = imagerotate($image,-90,0);
                    break;
            }
        }

       // $files = $request->file('files');
        foreach ($exif as $file) {
            $path = $file->storeAs('public/photos');

            $store_file[] = [

                'product_id' => $product->id,
                'filename' =>  $path
            ];
        }

Body:
As ther error mentiened exif_read_data() expects parameter 1 to be a valid path, array given.

You must provide image path from storage to exif_read_data() function which mean you need to loop through $request->file('files') and get the storage path via

$path = $file->store('public/photos');

Then

exif_read_data($path);

Fix

if($request->hasFile('files')){

    $store_file = [];

    foreach ($request->file('files') as $file) {

        $fileName = uniqid() . time() . '.' . $file->getClientOriginalExtension();

        $file->storeAs('public/photos', $fileName);

        $filePath = $file->getPathName();

        $exif = @exif_read_data($filePath);

        if(isset($exif['Orientation'])) {

            switch($exif['Orientation']) {
                case 8:
                    $deg = 90;
                    break;
                case 3:
                    $deg = 180;
                    break;
                case 6:
                    $deg = -90;
                    break;
            }

            if (isset($deg)) {

                $fullQualifiedFilePath = storage_path('app/public/photos/' . $fileName);

                if ($file->getClientOriginalExtension() == "png") {
                    $img_new = imagecreatefrompng($fullQualifiedFilePath);
                    $img_new = imagerotate($img_new, $deg, 0);

                    imagepng($img_new, $fullQualifiedFilePath);
                } else {
                    $img_new = imagecreatefromjpeg($fullQualifiedFilePath);
                    $img_new = imagerotate($img_new, $deg, 0);

                    imagejpeg($img_new, $fullQualifiedFilePath, 80);
                }
           }
       }

        $store_file[] = [

            'product_id' => $product->id,
            'filename' =>  'public/photos/' . $fileName
        ];
    }

    ProductsPhoto::insert($store_file);
}

Reference How to do a rotation of an image with PHP (Laravel)


问:
PHP How to hit a url and download its xml
I am trying to send this query:

```<code>http://api.geonames.org/search?featureCode=PRK&maxRows=10&username=demo&country=US&style=full&adminCode1=AK
</code>```

To a web service and pull down and parse a bunch of fields there, namely these:

```<code>// 1) totalResultsCount
// 2) name
// 3) lat
// 4) lng
// 5) countryCode
// 6) countryName
// 7) adminName1 - gives full state name
// 8) adminName2 - owner of the park.
</code>```

I am doing this:

```<code>$query_string = "http://api.geonames.org/search?featureCode=PRK&maxRows=10&username=demo&country=US&style=full&adminCode1=AK";
</code>```

Could someone please provide the right code to loop through the results and get values?

答:
Firstly, looks like that web service is returning XML rather than JSON.  You can use SimpleXML to parse that out.

Secondly, you may want to check out curl

An Example:

```<code>$ch = curl_init("http://api.geonames.org/search?featureCode=PRK&maxRows=10&username=demo&country=US&style=full&adminCode1=AK");

curl_setopt($ch, CURLOPT_HEADER, 0);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, TRUE);
$content = curl_exec($ch);

curl_close($ch);
</code>```

ContentLicense: CC BY-SA 3.0

Body:
I am trying to send this query:

http://api.geonames.org/search?featureCode=PRK&maxRows=10&username=demo&country=US&style=full&adminCode1=AK

To a web service and pull down and parse a bunch of fields there, namely these:

// 1) totalResultsCount
// 2) name
// 3) lat
// 4) lng
// 5) countryCode
// 6) countryName
// 7) adminName1 - gives full state name
// 8) adminName2 - owner of the park.

I am doing this:

$query_string = "http://api.geonames.org/search?featureCode=PRK&maxRows=10&username=demo&country=US&style=full&adminCode1=AK";

Could someone please provide the right code to loop through the results and get values?

Body:
Firstly, looks like that web service is returning XML rather than JSON.  You can use SimpleXML to parse that out.

Secondly, you may want to check out curl

An Example:

$ch = curl_init("http://api.geonames.org/search?featureCode=PRK&maxRows=10&username=demo&country=US&style=full&adminCode1=AK");

curl_setopt($ch, CURLOPT_HEADER, 0);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, TRUE);
$content = curl_exec($ch);

curl_close($ch);


问:
Returning back to main function from c or cpp series of sub functions
I wounder if there is any way to cancel execution of a function and return back to the caller from a sub - sub function?
Like in the assembly when you can do a pop from the stack address and make a return .. by that you can ignore the first return address to the previous address.
I have a hard time to find out a way to stop executing a piece of code when some error happens in a function which might be three way under the main function.. and return back to the main.
Like when you linux kernel or Windows command prompt run a program and ctrl-C stop the execution and return back to the shell.

Editing the original question:

My solution at the moment is like below (It is not clean):
What I use at the moment is: I have three macros. I use them in the beginning of all functions. But this costs me memory, speed and the flash of the Microcontroller

```<code>#define STOP_CODE_EXECUTION_MINUS   if(StopExecution==1) return -1;
#define STOP_CODE_EXECUTION         if(StopExecution==1) return;
#define STOP_CODE_EXECUTION_NULL    if(StopExecution==1) return 0;
</code>```

I choose the version that is suitable of the type of the function (void, int or string). I change the (StopExecution) whenever I get a problem in the OS I have. But if you have been using assembly, you know that you can pop/return to remove addresses from the stack and return back to the original caller (for ex. main).
My question form the beginning was about that. Is there a way to do the same using c/c++ code?
But, people here have PHD in programming and you have to ask them in that level.
never mind. I just added that to make it more clear for those who really cared and wanted to help thanks.

答:
In theory, yes. You can achieve something like that using techniques like longjmp.

In practice, however, it is really much easier, better (not to mention memory-safe in C++!) to solve the problem in another way. For example, you could use exceptions (in C++) or return error codes.

You would then have code like that (C example):

```<code>int some_failing_function() {
  if(failure) { return 1; }
  return 0;
}
int some_function() {
  if(some_failing_function() == 1) {
    return 1; /* return early and let caller know that we failed */
  }
  /* continue normally */
}
int main() {
  if(some_function() == 1) {
    fputs("failure!\n", stderr);
  }
}
</code>```

Update: If that becomes too complicated for you, you can use exceptions. You throw an exception when there is an error and you can later catch it:

```<code>#include <stdexcept>
void some_failing_function() {
  if(failure) { throw std::logic_error("something"); }
}
int some_function() {
  /* just use a normal function call here */
  some_failing_function();
  /* continue normally */
}
int main() {
  try {
    some_function();
  } catch(std::exception& e) {
    fputs("failure!\n", stderr);
  }
}
</code>```

Body:
I wounder if there is any way to cancel execution of a function and return back to the caller from a sub - sub function?
Like in the assembly when you can do a pop from the stack address and make a return .. by that you can ignore the first return address to the previous address.
I have a hard time to find out a way to stop executing a piece of code when some error happens in a function which might be three way under the main function.. and return back to the main.
Like when you linux kernel or Windows command prompt run a program and ctrl-C stop the execution and return back to the shell.

Editing the original question:

My solution at the moment is like below (It is not clean):
What I use at the moment is: I have three macros. I use them in the beginning of all functions. But this costs me memory, speed and the flash of the Microcontroller

#define STOP_CODE_EXECUTION_MINUS   if(StopExecution==1) return -1;
#define STOP_CODE_EXECUTION         if(StopExecution==1) return;
#define STOP_CODE_EXECUTION_NULL    if(StopExecution==1) return 0;

I choose the version that is suitable of the type of the function (void, int or string). I change the (StopExecution) whenever I get a problem in the OS I have. But if you have been using assembly, you know that you can pop/return to remove addresses from the stack and return back to the original caller (for ex. main).
My question form the beginning was about that. Is there a way to do the same using c/c++ code?
But, people here have PHD in programming and you have to ask them in that level.
never mind. I just added that to make it more clear for those who really cared and wanted to help thanks.

Body:
In theory, yes. You can achieve something like that using techniques like longjmp.

In practice, however, it is really much easier, better (not to mention memory-safe in C++!) to solve the problem in another way. For example, you could use exceptions (in C++) or return error codes.

You would then have code like that (C example):

int some_failing_function() {
  if(failure) { return 1; }
  return 0;
}
int some_function() {
  if(some_failing_function() == 1) {
    return 1; /* return early and let caller know that we failed */
  }
  /* continue normally */
}
int main() {
  if(some_function() == 1) {
    fputs("failure!\n", stderr);
  }
}

Update: If that becomes too complicated for you, you can use exceptions. You throw an exception when there is an error and you can later catch it:

#include <stdexcept>
void some_failing_function() {
  if(failure) { throw std::logic_error("something"); }
}
int some_function() {
  /* just use a normal function call here */
  some_failing_function();
  /* continue normally */
}
int main() {
  try {
    some_function();
  } catch(std::exception& e) {
    fputs("failure!\n", stderr);
  }
}


问:
Organize numbers in group ascending
Sorry for the mess, I´ll start from the beginning. I want this tibble:

A tibble: 20 x 2

```<code>  ID NAME
   <dbl> <chr>
 1     1 aa
 2     1 bb
 3     1 cc
 4     2 dd
 5     2 ee
 6     2 ff
 7     3 gg
 8     3 hh
 9     3 ii
10     4 jj
11     4 kk
12     4 ll
13     5 mm
14     5 nn
15     5 oo
16     6 pp
17     7 qq
18     8 rr
19     9 ss
20    10 tt
</code>```

Organized in this manner:

A tibble: 20 x 2

```<code>  ID NAME
   <dbl> <chr>
 1     1 aa
 2     2 dd
 3     3 gg
 4     4 jj
 5     5 mm
 6     6 pp
 7     7 qq
 8     8 rr
 9     9 ss
10    10 tt
11     1 bb
12     2 ee
13     3 hh
14     4 kk
15     5 nn
16     1 cc
17     2 ff
18     3 hh
19     4 ll
20     5 oo
</code>```

That is the first '1' followed by the first '2' and so on..

Thanks for all answers but I think my example was perhaps a bit misleading.

Best regards,

H

答:
Order by two columns, while keeping the order of NAME using factor:

```<code>df1[ order(factor(df1$NAME, levels = unique(df1$NAME)), df1$ID), ]
#    ID NAME
# 1   1   xx
# 4   2   xx
# 7   3   xx
# 10  4   xx
# 13  5   xx
# 16  6   xx
# 17  7   xx
# 18  8   xx
# 19  9   xx
# 20 10   xx
# 2   1   zz
# 5   2   zz
# 8   3   zz
# 11  4   zz
# 14  5   zz
# 3   1   yy
# 6   2   yy
# 9   3   yy
# 12  4   yy
# 15  5   yy
</code>```

data

```<code>df1 <- read.table(text = "
ID NAME
1 xx
1 zz
1 yy
2 xx
2 zz
2 yy
3 xx
3 zz
3 yy
4 xx
4 zz
4 yy
5 xx
5 zz
5 yy
6 xx
7 xx
8 xx
9 xx
10 xx", header = TRUE, stringsAsFactors = FALSE)
</code>```

Title: Organize numbers in group ascending

Body:
Sorry for the mess, I´ll start from the beginning. I want this tibble:

A tibble: 20 x 2

  ID NAME
   <dbl> <chr>
 1     1 aa
 2     1 bb
 3     1 cc
 4     2 dd
 5     2 ee
 6     2 ff
 7     3 gg
 8     3 hh
 9     3 ii
10     4 jj
11     4 kk
12     4 ll
13     5 mm
14     5 nn
15     5 oo
16     6 pp
17     7 qq
18     8 rr
19     9 ss
20    10 tt

Organized in this manner:

A tibble: 20 x 2

  ID NAME
   <dbl> <chr>
 1     1 aa
 2     2 dd
 3     3 gg
 4     4 jj
 5     5 mm
 6     6 pp
 7     7 qq
 8     8 rr
 9     9 ss
10    10 tt
11     1 bb
12     2 ee
13     3 hh
14     4 kk
15     5 nn
16     1 cc
17     2 ff
18     3 hh
19     4 ll
20     5 oo

That is the first '1' followed by the first '2' and so on..

Thanks for all answers but I think my example was perhaps a bit misleading.

Best regards,

H

Body:
Order by two columns, while keeping the order of NAME using factor:

df1[ order(factor(df1$NAME, levels = unique(df1$NAME)), df1$ID), ]
#    ID NAME
# 1   1   xx
# 4   2   xx
# 7   3   xx
# 10  4   xx
# 13  5   xx
# 16  6   xx
# 17  7   xx
# 18  8   xx
# 19  9   xx
# 20 10   xx
# 2   1   zz
# 5   2   zz
# 8   3   zz
# 11  4   zz
# 14  5   zz
# 3   1   yy
# 6   2   yy
# 9   3   yy
# 12  4   yy
# 15  5   yy

data

df1 <- read.table(text = "
ID NAME
1 xx
1 zz
1 yy
2 xx
2 zz
2 yy
3 xx
3 zz
3 yy
4 xx
4 zz
4 yy
5 xx
5 zz
5 yy
6 xx
7 xx
8 xx
9 xx
10 xx", header = TRUE, stringsAsFactors = FALSE)


问:
Postgres jsonb Index Format
I'm trying to find the name for these type of indexes. I looked on Google and Postgres documentation and they have nothing on what Value-> operator does. And how its different from a regular: -> SomeInt.

```<code>CREATE INDEX "someIndex" ON "me-db"."someTable" USING btree ((((jsondata -> 'SomeInt'::text) -> 'HasValue'::text)))
CREATE INDEX "someIndex2" ON "me-db"."someTable" USING btree ((((jsondata -> 'SomeInt'::text) -> 'Value'::text)))
</code>```

What are the names for ->HasValue and ->Value operators? How is ->Value different from ->Field.

答:
These are the key names in you json structure. If you wrap one with another it means you have a nested json like this (for the above lines):

{"SomeInt": {"HasValue": 1, "Value": 2}}

Title: Postgres jsonb Index Format

Body:
I'm trying to find the name for these type of indexes. I looked on Google and Postgres documentation and they have nothing on what Value-> operator does. And how its different from a regular: -> SomeInt.

CREATE INDEX "someIndex" ON "me-db"."someTable" USING btree ((((jsondata -> 'SomeInt'::text) -> 'HasValue'::text)))
CREATE INDEX "someIndex2" ON "me-db"."someTable" USING btree ((((jsondata -> 'SomeInt'::text) -> 'Value'::text)))

What are the names for ->HasValue and ->Value operators? How is ->Value different from ->Field.


问:
Mule ForEach Unable to get first value
I have a for each loop in my Mule 3 flow.

The Collection for the same is a flow variable array as shown below:

```<code>"[
  "Alpha",
  "Bravo",
  "Charlie",
  "Delta",
  "Echo",
]"
</code>```

Inside the for each, I have a session variable to store the current array value. I have set the value as #[payload]. The idea is to use each iterated ID to make an API update call. The issue is that, I am getting the complete payload as the current id session variable (as opposed to a single value). Any idea why it's not iterating 5 times (5 is the number of IDs that I have in the array).

答:
i couldnt reproduce your error. may be the way your array variable is defined is not proper.  i am not sure. I did a slight change.

```<code>    <flow name="loos_sample" doc:id="25ef89d0-dfcd-48c3-8c66-a94373a3b46c" >
        <http:listener doc:name="Listener" doc:id="96eca53c-6976-4b0e-9a18-9fd6d0ee6520" config-ref="HTTP_Listener_config" path="/loop"/>
        <ee:transform doc:name="Transform Message" doc:id="2ea9d7e3-ff1c-4ba4-99f2-40ae4c2d16e7" >
            <ee:message >
            </ee:message>
            <ee:variables >
                <ee:set-variable variableName="varIds" ><![CDATA[%dw 2.0
output application/java
---
{ arr:
[
  "Alpha",
  "Bravo",
  "Charlie",
  "Delta",
  "Echo",
]

}]]></ee:set-variable>
            </ee:variables>
        </ee:transform>
        <foreach doc:name="For Each" doc:id="972ee8d5-d8d6-43b6-866a-0894d1b3a450" collection="#[vars.varIds.arr]">
            <logger level="INFO" doc:name="Logger" doc:id="4478c587-f01c-4e2a-9485-5aa4b1be11df" message="curernt item: #[payload]"/>
        </foreach>
    </flow>
</code>```

Title: Mule ForEach Unable to get first value

Body:
I have a for each loop in my Mule 3 flow.

The Collection for the same is a flow variable array as shown below:

"[
  "Alpha",
  "Bravo",
  "Charlie",
  "Delta",
  "Echo",
]"

Inside the for each, I have a session variable to store the current array value. I have set the value as #[payload]. The idea is to use each iterated ID to make an API update call. The issue is that, I am getting the complete payload as the current id session variable (as opposed to a single value). Any idea why it's not iterating 5 times (5 is the number of IDs that I have in the array).

Body:
i couldnt reproduce your error. may be the way your array variable is defined is not proper.  i am not sure. I did a slight change.

    <flow name="loos_sample" doc:id="25ef89d0-dfcd-48c3-8c66-a94373a3b46c" >
        <http:listener doc:name="Listener" doc:id="96eca53c-6976-4b0e-9a18-9fd6d0ee6520" config-ref="HTTP_Listener_config" path="/loop"/>
        <ee:transform doc:name="Transform Message" doc:id="2ea9d7e3-ff1c-4ba4-99f2-40ae4c2d16e7" >
            <ee:message >
            </ee:message>
            <ee:variables >
                <ee:set-variable variableName="varIds" ><![CDATA[%dw 2.0
output application/java
---
{ arr:
[
  "Alpha",
  "Bravo",
  "Charlie",
  "Delta",
  "Echo",
]

}]]></ee:set-variable>
            </ee:variables>
        </ee:transform>
        <foreach doc:name="For Each" doc:id="972ee8d5-d8d6-43b6-866a-0894d1b3a450" collection="#[vars.varIds.arr]">
            <logger level="INFO" doc:name="Logger" doc:id="4478c587-f01c-4e2a-9485-5aa4b1be11df" message="curernt item: #[payload]"/>
        </foreach>
    </flow>


问:
Cannot read property 'viewContainerRef' of undefined
I am trying to display a dynamic component similar (not exact) to the example in angular docs.

I have a dynamic directive with viewContainerRef

```<code>@Directive({
   selector: '[dynamicComponent]'
})
export class DynamicComponentDirective {
   constructor(public viewContainerRef: ViewContainerRef) { }
}
</code>```

Excerpt from component code

```<code>@ViewChild(DynamicComponentDirective) adHost: DynamicComponentDirective;
..
ngAfterViewInit() {
let componentFactory = null;
                console.log(component);
                componentFactory = this.componentFactoryResolver.resolveComponentFactory(component);
                // this.adHost.viewContainerRef.clear();
                const viewContainerRef = this.adHost.viewContainerRef;
                viewContainerRef.createComponent(componentFactory);
}
</code>```

Finally added <ng-template dynamicComponent></ng-template> in template

答: You see this error when the directive does not construct. You can set a breakpoint on the directive's constructor and check if the breakpoint ever hits. If not, that means that you are not loading the directive correctly. Then you can check that your component that is loading the directive it is properly adding the directive into the template.</p>

Body:
I am trying to display a dynamic component similar (not exact) to the example in angular docs.

I have a dynamic directive with viewContainerRef

@Directive({
   selector: '[dynamicComponent]'
})
export class DynamicComponentDirective {
   constructor(public viewContainerRef: ViewContainerRef) { }
}

Excerpt from component code

@ViewChild(DynamicComponentDirective) adHost: DynamicComponentDirective;
..
ngAfterViewInit() {
let componentFactory = null;
                console.log(component);
                componentFactory = this.componentFactoryResolver.resolveComponentFactory(component);
                // this.adHost.viewContainerRef.clear();
                const viewContainerRef = this.adHost.viewContainerRef;
                viewContainerRef.createComponent(componentFactory);
}

Finally added <ng-template dynamicComponent></ng-template> in template


问:
ASP.NET Core - Serve index.html on all routes without rewriting the URL
creating an ASP.NET Core api. Is it possible to serve a SPA index.html on all paths that don't begin with "/api" without redirecting and rewriting the URL (thereby losing the URL for the SPA).</p>

答:
The SPA middleware that is used in both the Angular and the React templates for ASP.NET Core does exactly that. It will serve all remaining requests to the configured DefaultPage, defaulting to /index.html.

Just add the UseSpa() call as the latest middleware within the Startup’s Configure, after the UseEndpoint() call:

```<code>app.UseSpa(spa =>
{
    // configure the path to the folder where the static `index.html` is
    spa.Options.SourcePath = "ClientApp";
});
</code>```

Body:
The SPA middleware that is used in both the Angular and the React templates for ASP.NET Core does exactly that. It will serve all remaining requests to the configured DefaultPage, defaulting to /index.html.

Just add the UseSpa() call as the latest middleware within the Startup’s Configure, after the UseEndpoint() call:

app.UseSpa(spa =>
{
    // configure the path to the folder where the static `index.html` is
    spa.Options.SourcePath = "ClientApp";
});