Hi folks!

I have a small problem i'd like to solve:

Say i have 2 columns, both are nvarchar(20). the first one contains license
plates. I'd like to update the second column to contain the same as the
first, but with a space between letters and digits or viceversa.

examples:

col1: S XX12   col2: S XX 12
col1: 253BTC   col2: 253 BTC

How can i handle this in an sql script?

Thanks in advance!

Cheers... Pavel

Nice challenge. Here's something:
http://milambda.blogspot.com/2006/02/fun-with-strings.html


ML

---
http://milambda.blogspot.com/

Here is another way:

CREATE FUNCTION dbo.MyFunction (@inStr nvarchar(max), @delimiter
nvarchar(16))
RETURNS nvarchar(max)
AS
BEGIN
DECLARE @result nvarchar(max), @p1 int, @p2 int, @p int,
@delimiter_len int

IF @delimiter IS NULL SET @delimiter=' '

SET @delimiter_len=LEN(REPLACE(@delimiter,' ','_'))

set @result=''

WHILE 1=1 BEGIN
  SET @p1=PATINDEX('%[0-9][^0-9]%',@inStr)
  SET @p2=PATINDEX('%[^0-9][0-9]%',@inStr)

  SET @p=CASE WHEN @p1=0 THEN @p2 WHEN @p2=0 THEN @p1 WHEN @p1<@p2 THEN
@p1 ELSE @p2 END

  IF @p=0 BEGIN
   SET @result=@result+@inStr
   BREAK
  END

  SET @result=@result+LEFT(@inStr,@p)

  IF RIGHT(@result,@delimiter_len)<>@delimiter
   SET @result=@result+@delimiter

  SET @instr=SUBSTRING(@instr,@p+1,DATALENGTH(@instr))

  IF LEFT(@inStr,@delimiter_len)=@delimiter
   SET @inStr=SUBSTRING(@inStr,@delimiter_len+1,DATALENGTH(@instr))

END
RETURN @result
END

I think that my function performs better when the input string already
contains the delimiter. For example, try your function and mine with
the string 'S-XX12', using '-' as a delimiter.

Razvan

Razvan Socol <rso***@gmail.com> wrote:

Just finished my version... :-)

But thanks to both of you anyway!

Cheers... Pavel



if object_id('dbo.fn_SetDelimitedString_ByNumChar') IS NOT NULL
drop function dbo.fn_SetDelimitedString_ByNumChar
go

create function dbo.fn_SetDelimitedString_ByNumChar
(
@inStr  nvarchar(99),
@delimiter nvarchar(16) = null
)
returns nvarchar(99)
as
begin
declare @result nvarchar(99)
declare @curChar varchar(20)
declare @nextChar varchar(20)
declare @curCharCount int
declare @inStrLength int


if (@delimiter is null)
  begin
   set @delimiter = N' '
  end

set @inStrLength = len(@inStr)
set @curCharCount = 1
set @result  = ''


while (@curCharCount <= @inStrLength)

  begin
   set @curChar = substring(@inStr, @curCharCount, 1)
   set @nextChar = substring(@inStr, @curCharCount+1, 1)

   if ((@curChar != ' ') and (ISNUMERIC(@curChar)>0) and (not
ISNUMERIC(@nextChar)>0))
   or ((@curChar != ' ') and (ISNUMERIC(@nextChar)>0) and (not
ISNUMERIC(@curChar)>0))

    begin
     set @result = @result + @curChar + @delimiter
    end

   else

    begin
     set @result = @result + @curChar
    end

   set @curCharCount = @curCharCount + 1
  end

return ltrim(rtrim(@result))
end

go

Hi, Peter

Your function has some problems if the input string ends with a digit
and the delimiter is not space (try 'SXX12', with '-' as a delimiter).
Also, the ISNUMERIC function thinks that '.' is a number (try 'S.XX12',
with '-' as a delimiter), so it would be better to use LIKE '[0-9]'
instead of ISNUMERIC. For other problems regarding ISNUMERIC, see:
http://www.aspfaq.com/show.asp?id=2390

Razvan

Razvan Socol <rso***@gmail.com> wrote:

Thanks Razvan! You can use variable delimiters with ML's function, but I
only need <space> as a delimiter in my case (it's just a fire-and-forget
script :-)). The string is filtered beforehand, so "./-_*" can't be part of
it.

Thanks again.

Cheers... Pavel

Thanks for pointing that out, Razvan. I fixed that bug and found and fixed
another.


ML

---
http://milambda.blogspot.com/

Hmm... now your function doesn't work with spaces as a delimiter !

Here is my test data so far:

create table t (x varchar(max),d varchar(16))
insert into t values ('512btc', '-')
insert into t values ('FX788H', null)
insert into t values ('S XX12', null)
insert into t values ('S XX12OH', null)
insert into t values ('FX788H', '-')
insert into t values ('abc-99a', '-')
insert into t values ('abc 99a', ' ')
insert into t values ('S-XX12', '-')
insert into t values ('S XX12', ' ')
insert into t values ('FX788H', ' ')
insert into t values ('FX788H', '//')
insert into t values ('abc--99a', '-')
insert into t values ('abc  99a', '-')
insert into t values ('abc  99a', ' ')

select x,d,
dbo.fnGet_DelimitedString_byNumChar(x,d),
dbo.MyFunction(x,d)
from t

I guess that your problems are caused by the LEN function, which
returns "the number of characters [...] of the given string expression,
EXCLUDING TRAILING BLANKS".

Razvan

Thank you again. I had some trouble earlier with the datalength function, and
forgot to bring it back into use.

Of course now there are other issues. :) Well, thanks anyway.


ML

---
http://milambda.blogspot.com/

I think I got it now. And I've learned something about datalength and unicode.


ML

---
http://milambda.blogspot.com/

Concatenating:www.aspfaq.com/show.asp?id=2529 Is the glass half full or half empty or..?
Query help - resultset too large... impossible!
Used Disk Space reporting and sending Email
Problem updating two tables in a transaction.
wtf - sorting table doesnt work????