How do I extract part of a string in t-sql

Question

If I have the following nvarchar variable - BTA200  how can I extract just the BTA from it   Also  if I have varying lengths such as BTA50  BTA030  how can I extract just the numeric part

User · Answer

LEFT   BTA200   3  will work for the examples you have given  as in    SELECT LEFT MyField  3  FROM MyTable   To extract the numeric part  you can use this code  SELECT RIGHT MyField  LEN MyField  - 3  FROM MyTable WHERE MyField LIKE  BTA    --Only have this test if your data does not always start with BTA

User · Answer

substring field  1 3  will work on your examples   select substring field  1 3  from table   Also  if the alphabetic part is of variable length  you can do this to extract the alphabetic part   select substring field  1  PATINDEX    1234567890     field  -1   from table where PATINDEX    1234567890     field   gt  0

User · Answer

substring field  1 3  will work on your examples   select substring field  1 3  from table   Also  if the alphabetic part is of variable length  you can do this to extract the alphabetic part   select substring field  1  PATINDEX    1234567890     field  -1   from table where PATINDEX    1234567890     field   gt  0

User · Answer

declare  data as varchar 50  set  data  ciao335    --get text Select Left  Data  PatIndex    0-9      Data    1   - 1     ---- gt  gt ciao  --get numeric Select right  Data  len  data  -  PatIndex    0-9      Data  -1      ---- gt  gt 335

User · Answer

I would recommend a combination of PatIndex and Left   Carefully constructed  you can write a query that always works  no matter what your data looks like   Ex   Declare  Temp Table Data VarChar 20    Insert Into  Temp Values  BTA200   Insert Into  Temp Values  BTA50   Insert Into  Temp Values  BTA030   Insert Into  Temp Values  BTA   Insert Into  Temp Values  123   Insert Into  Temp Values  X999    Select Data  Left Data  PatIndex    0-9     Data    1   - 1  From    Temp   PatIndex will look for the first character that falls in the range of 0-9  and return it s character position  which you can use with the LEFT function to extract the correct data   Note that PatIndex is actually using Data    1    This protects us from data where there are no numbers found   If there are no numbers  PatIndex would return 0   In this case  the LEFT function would error because we are using Left Data  PatIndex - 1    When PatIndex returns 0  we would end up with Left Data  -1  which returns an error   There are still ways this can fail   For a full explanation  I encourage you to read   Extracting numbers with SQL Server  That article shows how to get numbers out of a string   In your case  you want to get alpha characters instead   However  the process is similar enough that you can probably learn something useful out of it

User · Answer

declare  data as varchar 50  set  data  ciao335    --get text Select Left  Data  PatIndex    0-9      Data    1   - 1     ---- gt  gt ciao  --get numeric Select right  Data  len  data  -  PatIndex    0-9      Data  -1      ---- gt  gt 335

User · Answer

I would recommend a combination of PatIndex and Left   Carefully constructed  you can write a query that always works  no matter what your data looks like   Ex   Declare  Temp Table Data VarChar 20    Insert Into  Temp Values  BTA200   Insert Into  Temp Values  BTA50   Insert Into  Temp Values  BTA030   Insert Into  Temp Values  BTA   Insert Into  Temp Values  123   Insert Into  Temp Values  X999    Select Data  Left Data  PatIndex    0-9     Data    1   - 1  From    Temp   PatIndex will look for the first character that falls in the range of 0-9  and return it s character position  which you can use with the LEFT function to extract the correct data   Note that PatIndex is actually using Data    1    This protects us from data where there are no numbers found   If there are no numbers  PatIndex would return 0   In this case  the LEFT function would error because we are using Left Data  PatIndex - 1    When PatIndex returns 0  we would end up with Left Data  -1  which returns an error   There are still ways this can fail   For a full explanation  I encourage you to read   Extracting numbers with SQL Server  That article shows how to get numbers out of a string   In your case  you want to get alpha characters instead   However  the process is similar enough that you can probably learn something useful out of it

User · Answer

I would recommend a combination of PatIndex and Left   Carefully constructed  you can write a query that always works  no matter what your data looks like   Ex   Declare  Temp Table Data VarChar 20    Insert Into  Temp Values  BTA200   Insert Into  Temp Values  BTA50   Insert Into  Temp Values  BTA030   Insert Into  Temp Values  BTA   Insert Into  Temp Values  123   Insert Into  Temp Values  X999    Select Data  Left Data  PatIndex    0-9     Data    1   - 1  From    Temp   PatIndex will look for the first character that falls in the range of 0-9  and return it s character position  which you can use with the LEFT function to extract the correct data   Note that PatIndex is actually using Data    1    This protects us from data where there are no numbers found   If there are no numbers  PatIndex would return 0   In this case  the LEFT function would error because we are using Left Data  PatIndex - 1    When PatIndex returns 0  we would end up with Left Data  -1  which returns an error   There are still ways this can fail   For a full explanation  I encourage you to read   Extracting numbers with SQL Server  That article shows how to get numbers out of a string   In your case  you want to get alpha characters instead   However  the process is similar enough that you can probably learn something useful out of it

User · Answer

LEFT   BTA200   3  will work for the examples you have given  as in    SELECT LEFT MyField  3  FROM MyTable   To extract the numeric part  you can use this code  SELECT RIGHT MyField  LEN MyField  - 3  FROM MyTable WHERE MyField LIKE  BTA    --Only have this test if your data does not always start with BTA

User · Answer

substring field  1 3  will work on your examples   select substring field  1 3  from table   Also  if the alphabetic part is of variable length  you can do this to extract the alphabetic part   select substring field  1  PATINDEX    1234567890     field  -1   from table where PATINDEX    1234567890     field   gt  0

User · Answer

substring field  1 3  will work on your examples   select substring field  1 3  from table   Also  if the alphabetic part is of variable length  you can do this to extract the alphabetic part   select substring field  1  PATINDEX    1234567890     field  -1   from table where PATINDEX    1234567890     field   gt  0

User · Answer

I would recommend a combination of PatIndex and Left   Carefully constructed  you can write a query that always works  no matter what your data looks like   Ex   Declare  Temp Table Data VarChar 20    Insert Into  Temp Values  BTA200   Insert Into  Temp Values  BTA50   Insert Into  Temp Values  BTA030   Insert Into  Temp Values  BTA   Insert Into  Temp Values  123   Insert Into  Temp Values  X999    Select Data  Left Data  PatIndex    0-9     Data    1   - 1  From    Temp   PatIndex will look for the first character that falls in the range of 0-9  and return it s character position  which you can use with the LEFT function to extract the correct data   Note that PatIndex is actually using Data    1    This protects us from data where there are no numbers found   If there are no numbers  PatIndex would return 0   In this case  the LEFT function would error because we are using Left Data  PatIndex - 1    When PatIndex returns 0  we would end up with Left Data  -1  which returns an error   There are still ways this can fail   For a full explanation  I encourage you to read   Extracting numbers with SQL Server  That article shows how to get numbers out of a string   In your case  you want to get alpha characters instead   However  the process is similar enough that you can probably learn something useful out of it

User · Answer

LEFT   BTA200   3  will work for the examples you have given  as in    SELECT LEFT MyField  3  FROM MyTable   To extract the numeric part  you can use this code  SELECT RIGHT MyField  LEN MyField  - 3  FROM MyTable WHERE MyField LIKE  BTA    --Only have this test if your data does not always start with BTA

User · Answer

LEFT   BTA200   3  will work for the examples you have given  as in    SELECT LEFT MyField  3  FROM MyTable   To extract the numeric part  you can use this code  SELECT RIGHT MyField  LEN MyField  - 3  FROM MyTable WHERE MyField LIKE  BTA    --Only have this test if your data does not always start with BTA

[sql-server] How do I extract part of a string in t-sql

Examples related to sql-server

Examples related to tsql

Examples related to string