I've looked at this link, but it does not really answer my question.

Developing a Function for this would be ideal.

I developed a trigger to insert the following into a table using one column:

create trigger kv_trg_AssetAdjustment_AW on _btblInvoiceLines       --select * from _btblInvoiceLines
after insert update

if (select trigger_nestlevel(object_id('kv_trg_AssetAdjustment_AW'))) > 1


    declare @Asset      varchar(40)
    ,       @Desc       varchar(100)
    ,       @AssetValue float
    ,       @Price      float
    ,       @Qty        float
    ,       @Code       varchar(100)
    ,       @Valid      int
    ,       @Exists     int
    ,       @SL         int
    ,       @NextNum    int
    ,       @Variable   varchar(100)
    ,       @NewCode    varchar(100)
    ,       @Err        nvarchar(500)
    ,       @Err2       nvarchar(500)
    ,       @Err3       nvarchar(500)

    select  @Err    =   '--------------------------';
    select  @Err    =   @Err    +   @Err    +   CHAR(10);
    select  @Err    =   @Err    +   CHAR(10);
    select  @Err    =   @Err    +   'Please specify a hyphen ("-") between Item Code & Description in the Description!';

        @Asset      =   ulIDPOrdTxGLVAT
    from    inserted

        if  (@Asset = 'Asset')

                if exists(select cDescription from inserted where cDescription  like '%-%')
                    select  @Valid  =   1
                        raiserror(@Err, 16, 1)

                    @Code       =   upper(LEFT(cDescription, CHARINDEX('-', cDescription) - 1))
                ,   @Desc       =   REPLACE(SUBSTRING(cDescription, CHARINDEX('-', cDescription), LEN(cDescription)), '-', '')
                ,   @AssetValue =   fQuantityLineTotExcl
                ,   @Qty        =   fQuantity
                from    inserted

                select  @Err2   =   '--------------------------';
                select  @Err2   =   @Err2   +   @Err2   +   CHAR(10);
                select  @Err2   =   @Err2   +   CHAR(10);
                select  @Err2   =   @Err2   +   'Item : ' + @Code + ' already exists!';
                select  @Err2   =   @Err2   +   CHAR(10);
                select  @Err2   =   @Err2   +   'Please specify a different Item Code!';

                select  @Err3   =   '--------------------------';
                select  @Err3   =   @Err3   +   @Err3   +   CHAR(10);
                select  @Err3   =   @Err3   +   CHAR(10);
                select  @Err3   =   @Err3   +   'Please specify a numeric value at the end of the Item Code';

                if exists(select cDescription from inserted where cDescription like '%[^0-9]%')
                    select  @Valid  =   1
                        raiserror(@Err3, 16, 1)

                if exists(select Code from StkItem where Code = @Code)
                        raiserror(@Err2, 16, 1)
                    select  @Exists =   0

                    if  (@Exists = 0)
                            insert into StkItem     (
                            ,       cSimpleCode
                            ,       Description_1
                            ,       Description_2
                            ,       TTI
                            ,       TTC
                            ,       TTG
                            ,       TTR
                            ,       WhseItem
                            ,       ubIIAsset
                            select  @Code
                            ,       @Code
                            ,       @Desc
                            ,       @AssetValue
                            ,       1
                            ,       1
                            ,       1
                            ,       1
                            ,       1
                            ,       1
                update  _btblInvoiceLines
                    set ufIDPOrdTxGLAssetValue          =   @AssetValue
                from    _btblInvoiceLines   L
                join    inserted on L.idInvoiceLines    =   inserted.idInvoiceLines

As can be seen from the code above, I'm splitting data in the cDescription Column whereby the inserted data will look like the following : BPO0005-My Item Description. Using the hyphen, I'm able to split the one column into two columns i.e. Item Code & Description.

As you may know users, they might forget to type a numeric value behind the Item Code, so I have to raise errors when the data is invalid.

I've tried isnumeric(cdescription), but it does not provide me with the results I require.

What I need is, if the value input from the user is BPO-My Item Description, my trigger should prohibit the user from continuing. Hence I looked at splitting this value to validate it, but I'm all out of ideas.

Please advise on how I will achieve this?

  • 2
    why are you doing this kind of data type conversion and enforcement in the data layer? It's far better (and easier) (and responsive to the users) to do this at the application layer. Your trigger also does not support working with multiple records at a time. – Jonathan Fite Sep 25 '18 at 19:50
  • How does ISNUMERIC() not solve your problem? You're splitting the input into an alphanumeric code and a description, and then splitting the code into an alphabetic prefix and a numeric suffix, yes? It would be helpful to strip your code down to the exact elements needed to reproduce the problem; people will be reluctant to read 117 lines of code. – Jon of All Trades Sep 25 '18 at 19:50
  • @Jon of All Trades, when I specify isnumeric for like the following, the result is 0, whereby I need it to be 1, because it input value has numbers and characters. Only when the input value is characters, should my trigger block it. When I use the following: declare @var varchar(30) = '001' select isnumeric(@var) The result is 1, but when I add a character in front of it, the result is 0 – Birel Sep 25 '18 at 19:53
  • Yes, you will need to split your alphanumeric code into a prefix and suffix first, using whatever business rules are appropriate. You may be able to use a simple LIKE '%[0-9] predicate. However, text manipulation in SQL is better served on Stack Overflow (and, as the other Jonathan says, outside of SQL entirely); the DBA site is meant for more specialized questions. – Jon of All Trades Sep 25 '18 at 20:00
  • @Birel - Yes - because then the value is no longer numeric. – RDFozz Sep 25 '18 at 20:00

I never used sql server myself (only PostgreSQL , Oracle and Mysql), but i think this will help:

select substring([col], PatIndex('%[0-9]%', [col]), len([col]))

where col is a string 'BP00005'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.