Seperate a string from a certain character on

• Wallertown

  SSC Rookie

  Points: 27

  More actions

  April 11, 2012 at 3:22 am

  #254652

  Hi

  I'm new to SQL and hope you can help.

  I want the following:

  I want a select query which selects the weight-field of a datatable.

  But it shall only select the weight in gram.

  Here's an example of a field: "kg;-3;12921"

  But not all the weights are equally long and the number of chars before

  the weight may differ also.

  So i wanted to do something like : take the chars after the second semicolon

  until the end of the string .

  Or if it's easier take the chars beginning from the end of the string until the first semicolon.

  Something like

  SELECT weight = (take the chars after the second semicolon

  until the end of the string)

  FROM balance

  Thanks in advance

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  April 11, 2012 at 3:31 am

  #1471731

  Wallertown (4/11/2012)

  ---

  Hi

  I'm new to SQL and hope you can help.

  I want the following:

  I want a select query which selects the weight-field of a datatable.

  But it shall only select the weight in gram.

  Here's an example of a field: "kg;-3;12921"

  But not all the weights are equally long and the number of chars before

  the weight may differ also.

  So i wanted to do something like : take the chars after the second semicolon

  until the end of the string .

  Or if it's easier take the chars beginning from the end of the string until the first semicolon.

  Something like

  SELECT weight = (take the chars after the second semicolon

  until the end of the string)

  FROM balance

  Thanks in advance

  SELECT STUFF([weight],1, CHARINDEX ( ';',[weight], CHARINDEX(';',[weight])+1),'') AS [weight]

  FROM balance;

  Here's a test run -

  DECLARE @test VARCHAR(30) = 'kg;-3;12921';

  SELECT STUFF(@test,1, CHARINDEX ( ';',@test, CHARINDEX(';',@test)+1),'');

  Returns:

  12921

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---

• Wallertown

  SSC Rookie

  Points: 27

  More actions

  April 11, 2012 at 3:41 am

  #1471736

  Thanks a lot for the more than quick soultion :w00t:

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  April 11, 2012 at 4:43 am

  #1471745

  Wallertown (4/11/2012)

  ---

  Thanks a lot for the more than quick soultion :w00t:

  No problem. Do you understand how it works?

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---

• Wallertown

  SSC Rookie

  Points: 27

  More actions

  April 11, 2012 at 6:10 am

  #1471789

  I've looked up STUFF and CHARINDEX in the ctrl+F1 help and I think I understood it ^^ thx