work with sub string - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

work with sub string

Author

Topic

vux
Starting Member

45 Posts

Posted - 2004-08-19 : 23:02:53

Hello,

I have column (nvarchar)called organisation, the values look typically like that:

"abd def ghi jkl"

Now I want to fill a new column called division with the substring "def"
-> word between the 1st and 2nd blank space.

How can I do that on the SQL Server, define a formula for the organisation field or exectute a new SQL task in my DTS Package?

How would the formula/SQL task look like?

Thanks for your help!

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-08-19 : 23:26:53

declare @space1 int, @space2 int

SET @space1 = CharIndex(' ', fieldName)
SET @space2 = CharIndex(' ', fieldName, @space1)

SET newField = SubString(fieldName, @space1, @space2-@space1)

Is the general gist of it. You'll probably need to fine-tune the character placements to get it right because I haven't tested it.

vux
Starting Member

45 Posts

Posted - 2004-08-20 : 00:55:56

Hi Timmy,
thanks a lot! yes i did not notice the edit button..

now for the sql task, which order is right, like that?

I always get the error that "TravelerOrgAbbr"is an invalid object, why cant it be found? its in tblIMISTravelExtras

DECLARE @space1 int, @space2 int
SET @space1 = CharIndex(' ',TravelerOrgAbbr)
SET @space2 = CharIndex(' ', TravelerOrgAbbr, @space1)

UPDATE tblIMISTravelExtras
SET DivisionCode = SubString(TravelerOrgAbbr, @space1, @space2-@space1)

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-08-20 : 01:21:06

SQL doesn't know what TravelerOrgAbbr is. So, either you need to extract the TravelerOrgAbbr value into a variable:

Declare @cTravelerOrgAbbr varchar(30)
SELECT @cTravelerOrgAbbr FROM table WHERE ....

Then run the statements:

SET @space1 = CharIndex(' ',@cTravelerOrgAbbr)
SET @space2 = CharIndex(' ', @cTravelerOrgAbbr, @space1)

UPDATE tblIMISTravelExtras
SET DivisionCode = SubString(@cTravelerOrgAbbr, @space1, @space2-@space1)

If you're doing this a lot you might be better off creating a function as it will make your coding much cleaner.

Tim

vux
Starting Member

45 Posts

Posted - 2004-08-20 : 02:46:04

hi again,
i am using this thing only once,

unfortunately

SELECT @cTravelerOrgAbbr FROM tblIMISTravelExtras

just has NULL values (but there are no NUll values for TravelerOrgAbbr), consequentally the rest of the query does not work.

Are you sure about the syntax of that line

vux
Starting Member

45 Posts

Posted - 2004-08-20 : 04:02:52

I think I got the syntax right now, but the command is wrong sice the subqueries return moire than one value.

DECLARE @space1 int, @space2 int

SET @space1 = (SELECT (CharIndex(' ',TravelerOrgAbbr)+1) FROM tblIMISTravelExtras)
SET @space2 = (SELECT (CharIndex(' ',TravelerOrgAbbr, @space1)-1)FROM tblIMISTravelExtras)

UPDATE tblIMISTravelExtras
SET DivisionCode = SubString(TravelerOrgAbbr, @space1, @space2-@space1)

I need to use arrays for @space and the write a loop to update TravelExtras I think - no clue if this is possible...

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-08-20 : 04:29:52

If you wanted to be really messy you could do it all in one query:

UPDATE tblIMISTravelExtras
SET DivisionCode = SubString(TravelerOrgAbbr, (CharIndex(' ',TravelerOrgAbbr)+1), (CharIndex(' ',TravelerOrgAbbr, (CharIndex(' ',TravelerOrgAbbr)+1))-1(CharIndex(' ',TravelerOrgAbbr)+1))

vux
Starting Member

45 Posts

Posted - 2004-08-23 : 03:54:30

Hi again,

I alreagy tried that, but the server does not allow that

it is not possible to substitue the lenght parameter of the substring function, with a variable I think...

I am writing a fct now on the server...

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-08-23 : 04:38:04

The STUFF function can be incredibly useful at times like these:


UPDATE tblIMISTravelExtras
SET DivisionCode = STUFF(TravelerOrgAbbr, CHARINDEX(' ', TravelerOrgAbbr), 1, ' def ')

Does that help?

OS

vux
Starting Member

45 Posts

Posted - 2004-08-23 : 22:31:15

Hi,

I think stuff does not really what I need, but goo dto know about this fct anyways...

Deletes a specified length of characters and inserts another set of characters at a specified starting point.

Syntax
STUFF ( character_expression , start , length , character_expression )

Here is a working fct, unfortunately I can't use it in the formula field of the column

CREATE FUNCTION fn_getDiv (@txt AS nvarchar(70))
RETURNS nvarchar(8)
AS
BEGIN
DECLARE @result nvarchar(8), @space1 int, @space2 int
SELECT @space1 = CharIndex(' ',@txt)+1
SELECT @space2 = CharIndex(' ',@txt, @space1)-1

SELECT @result = SubString(@txt, @space1, @space2-@space1)
RETURN @result
END

SQLServerSpotlight
Yak Posting Veteran

57 Posts

Posted - 2004-08-24 : 02:18:07


-- (This is my first post and I'm having problems with the formatting, appoligies)
-- I dislike the use of variables, as it opten leads to a loop
-- and a cursor.  So.....

-- we need some data
drop table      #forum_test1
create table    #forum_test1 (s sysname)
insert into     #forum_test1 select 'abc def ghi jkl'


-- Firstly we want to tokenise the string, so we construct sql
-- with levels of sub-selects to reduce the masses of charindex and substring calls.

    -- SQL BLOCK 1
    select  *
        ,   case when p3=0 then 0 else charindex(' ', s, p3+1) end p4
    from    (
        select  *
            ,   case when p2=0 then 0 else charindex(' ', s, p2+1) end p3
        from    (
            select  *
                ,   case when p1=0 then 0 else charindex(' ', s, p1+1) end p2
            from    (
                --    Put your select..from statement here. (keeping length, s and p1)
                select len(s)    length
                    ,  s
                    ,  charindex(' ', s)    p1
                from   #forum_test1
            )    t1
        )    t2
    )    t3
    
-- Secondly we need to correct the positions so that the 'p' values are increasing.
-- (This is so our following substring calls are nice)
    -- SQL BLOCK 2
    select    s
        ,    1    p0
        ,    case when p1 = 0 then length+1 else p1 end p1    -- These are needed to ensure that the 'p' (positions) are ever increasing
        ,    case when p2 = 0 then length+1 else p2 end p2
        ,    case when p3 = 0 then length+1 else p3 end p3
        ,    case when p4 = 0 then length+1 else p4 end p4
    from    (
        -- SQL BLOCK 1
        select  *
            ,   case when p3=0 then 0 else charindex(' ', s, p3+1) end p4
        from    (
            select  *
                ,   case when p2=0 then 0 else charindex(' ', s, p2+1) end p3
            from    (
                select  *
                    ,   case when p1=0 then 0 else charindex(' ', s, p1+1) end p2
                from    (
                    --    Put your select..from statement here. (keeping length, s and p1)
                    select len(s)    length
                        ,  s
                        ,  charindex(' ', s)    p1
                    from   #forum_test1
                )    t1
            )    t2
        )    t3
    )    t4

-- Thirdly we construct the Tokens so we are ready to use them for what ever purpose we may have.
    select   s
        ,    substring(s,p0,  p1-p0)      s1
        ,    substring(s,p1+1,p2-p1-1)    s2    -- '+1' for the prepended-space, '-1' for the length adjustement from the space
        ,    substring(s,p2+1,p3-p2-1)    s3    -- ditto
        ,    substring(s,p3+1,p4-p3-1)    s4    -- ditto
    from    (
        -- SQL BLOCK 2
        select   s
            ,    1    p0    -- to make our sql above nice and regular
            ,    case when p1 = 0 then length+1 else p1 end p1    -- These are needed to ensure that the 'p' (positions) are ever increasing
            ,    case when p2 = 0 then length+1 else p2 end p2    -- ditto
            ,    case when p3 = 0 then length+1 else p3 end p3    -- ditto
            ,    case when p4 = 0 then length+1 else p4 end p4    -- ditto
        from    (
            -- SQL BLOCK 1
            select  *
                ,   case when p3=0 then 0 else charindex(' ', s, p3+1) end p4
            from    (
                select  *
                    ,   case when p2=0 then 0 else charindex(' ', s, p2+1) end p3
                from    (
                    select  *
                        ,   case when p1=0 then 0 else charindex(' ', s, p1+1) end p2
                    from    (
                        select  len(s)    length
                            ,   s
                            ,   charindex(' ', s)    p1
                        from    #forum_test1
                    )    t1
                )    t2
            )    t3
        )    t4
    )    t

-- The SQL Optimizer does a good job of dealing with the sub-selects.
--

vux
Starting Member

45 Posts

Posted - 2004-08-24 : 03:16:08

Ok guys,

now it works, the solution, very simple:

1) define local function
CREATE FUNCTION fn_getDiv (@txt AS nvarchar(70))
RETURNS nvarchar(8)
AS
BEGIN
DECLARE @result nvarchar(8), @space1 int, @space2 int
SELECT @space1 = CharIndex(' ',@txt)+1
SELECT @space2 = CharIndex(' ',@txt, @space1)-1

2)run sql task
UPDATE tblIMISTravelExtras
SET Division = owner.fn_getDiv(tblIMISTravelExtras.TravelerOrgAbbr)

that was all I needed

of course the function is not flexible it always get the 2nd owrk of the string, but that is sufficient right now

thanks for your posts!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources