Other approach for handling this TSQL text manipulation
I have this following data:
0297144600-4799 0297485500-5599
The 0297485500-5599 based on observation always on position 31 char from the left which this is an easy approach.
But I would like to do is to anticipate just in case the data is like this below which means the position is no longer valid:
0297144600-4799 0297485500-5599 0297485600-5699
As you can see, I guess the first approach will the split by 1 blank space (" ") b开发者_如何学运维ut due to number of space is unknown (varies) how do I take this approach then? Is there any method to find the space in between and shrink into 1 blank space (" ").
BTW ... it needs to be done in TSQL (Ms SQL 2005) unfortunately cause it's for SSIS :(
I am open with your idea/suggestion.
Thanks
I have updated my answer a bit, now that I know the number pattern will not always match. This code assumes the sequences will begin and end with a number and be separated by any number of spaces.
DECLARE @input nvarchar -- max in parens
DECLARE @pattern nvarchar -- max in parens
DECLARE @answer nvarchar -- max in parens
DECLARE @pos int
SET @input = ' 0297144623423400-4799 5615618131201561561 0297485600-5699 '
-- Make sure our search string has whitespace at the end for our pattern to match
SET @input = @input + ' '
-- Find anything that starts and ends with a number
WHILE PATINDEX('%[0-9]%[0-9] %', @input) > 0
BEGIN
-- Trim off the leading whitespace
SET @input = LTRIM(@input)
-- Find the end of the sequence by finding a space
SET @pos = PATINDEX('% %', @input)
-- Get the result out now that we know where it is
SET @answer = SUBSTRING(@input, 0, @pos)
SELECT [Result] = @answer
-- Remove the result off the front of the string so we can continue parsing
SET @input = SUBSTRING(@input, LEN(@answer) + 1, 8096)
END
Assuming you're processing one line at a time, you can also try this:
DECLARE @InputString nvarchar(max)
SET @InputString = '0297144600-4799 0297485500-5599 0297485600-5699'
BEGIN
WHILE CHARINDEX(' ',@InputString) > 0 -- Checking for double spaces
SET @InputString =
REPLACE(@InputString,' ',' ') -- Replace 2 spaces with 1 space
END
PRINT @InputString
(taken directly from SQLUSA, fnRemoveMultipleSpaces1)
精彩评论