Return txt from string

• 2Tall

  SSChampion

  Points: 14644

  More actions

  June 7, 2010 at 3:49 am

  #387089

  Hi. I have a column in a database that lists license key information. The data (NVARCHAR (64)) is held in the following format:

  7487-OEM-00011193-0010(Key: HHYUR-86HZH-99V96-JKL0-88895). The string before the opening bracket differs in length.

  I would like to only extract the string at starting point: Key:

  Any ideas.

  Thanks,

  Phil.

  -------------------------------------------------------------------------------------
  A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

  Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

  Tommy Cooper

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  June 7, 2010 at 3:57 am

  #1176982

  Hey Phil, is this what you are looking for?

  DECLARE @STR VARCHAR(64)

  SET @STR = '7487-OEM-00011193-0010(Key: HHYUR-86HZH-99V96-JKL0-88895)'

  SELECT SUBSTRING(@str,( CHARINDEX('Key:',@str)+5 ) , (CHARINDEX ('-',@str,CHARINDEX('Key:',@str)) - (CHARINDEX('Key:',@str)+5 ) ) ) KEY_FIRST_PART

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  June 7, 2010 at 4:00 am

  #1176983

  Broadening the input result set:

  Sample table and sample data

  DECLARE @table TABLE ( STRING VARCHAR(64) )

  INSERT INTO @table

  SELECT '7487-OEM-00011193-0010(Key: HHYUR-86HZH-99V96-JKL0-88895)'

  UNION ALL SELECT '7487-OEM-00011193-0010(Key: A-86HZH-99V96-JKL0-88895)'

  UNION ALL SELECT '7487-OEM-00011193-0010(Key: BB-86HZH-99V96-JKL0-88895)'

  UNION ALL SELECT '7487-OEM-00011193-0010(Key: CCC-86HZH-99V96-JKL0-88895)'

  UNION ALL SELECT '7487-OEM-00011193-0010(Key: DDDD-86HZH-99V96-JKL0-88895)'

  UNION ALL SELECT '7487-OEM-00011193-0010(Key: EEEEE-86HZH-99V96-JKL0-88895)'

  UNION ALL SELECT '7487-OEM-00011193-0010(Key: FFFFFF-86HZH-99V96-JKL0-88895)'

  Code that will fetch the first part of the key that comes after teh word "Key:"

  SELECT SUBSTRING(STRING,( CHARINDEX('Key:',STRING)+5 ) , (CHARINDEX ('-',STRING,CHARINDEX('Key:',STRING)) - (CHARINDEX('Key:',STRING)+5 ) ) ) KEY_FIRST_PART

  FROM @table

  Hope this helps you!

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  June 7, 2010 at 4:04 am

  #1176984

  If thats NOT what you wanted, then please tel us your desired result based on the input recored set i provided..

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  June 7, 2010 at 4:10 am

  #1176987

  Something like this?

  I've returned both the start bit and the end bit, basically getting rid of the "Key" part, because I wasn't entirely sure what you're after

  (code has been edited, found a bug)

  DECLARE @string AS VARCHAR(100)

  DECLARE @separator AS VARCHAR(50)

  SET @string = '7487-OEM-00011193-0010(Key: HHYUR-86HZH-99V96-JKL0-88895)'

  SET @separator = '(Key:'

  ;WITH split

  AS (SELECT 0 startpart,

  1 endpart

  UNION ALL

  SELECT endpart,

  Charindex(@separator, @string, endpart) + Len(@separator)

  FROM split

  WHERE endpart > startpart)

  SELECT REPLACE(REPLACE(Substring(@string, startpart,

  CASE

  WHEN

  endpart > Len(@separator)

  THEN

  endpart - startpart - Len

  (

  @separator)

  ELSE

  Len(@string) - startpart + 1

  END), ' ', ''), ')', '')

  string

  FROM split

  WHERE startpart > 0

  ---

  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/

  ---

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  June 7, 2010 at 4:28 am

  #1176994

  If your request is what Sckadavre is saying, then this might help you! This code doesn use a self-referencing CTE, but assuming your string contains only one Key1: "

  DECLARE @DELIMITER VARCHAR(10)

  SELECT @DELIMITER = '(Key: '

  SELECT

  REPLACE ( (LEFT(STRING , (CHARINDEX(@DELIMITER,STRING)))) ,'(', '') OEM_STRING,

  SUBSTRING( STRING, (CHARINDEX(@DELIMITER,STRING)+ 5 ) , ((DATALENGTH(STRING)) - (CHARINDEX(@DELIMITER,STRING)+ 5 )) ) KEY_NUM

  FROM @table

  ~Edit : Removed -1 from DATALENGTH to give the precise KEY

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  June 7, 2010 at 4:42 am

  #1177003

  ColdCoffee (6/7/2010)

  ---

  If your request is what Sckadavre is saying, then this might help you! This code doesn use a self-referencing CTE, but assuming your string contains only one Key1: "

  DECLARE @DELIMITER VARCHAR(10)

  SELECT @DELIMITER = '(Key: '

  SELECT

  REPLACE ( (LEFT(STRING , (CHARINDEX(@DELIMITER,STRING)))) ,'(', '') OEM_STRING,

  SUBSTRING( STRING, (CHARINDEX(@DELIMITER,STRING)+ 5 ) , ((DATALENGTH(STRING)-1) - (CHARINDEX(@DELIMITER,STRING)+ 5 )) ) KEY_NUM

  FROM @table

  You fell for the same thing as me, "KEY_NUM" contains a prefix space. 🙂

  ---

  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/

  ---

• 2Tall

  SSChampion

  Points: 14644

  More actions

  June 7, 2010 at 4:45 am

  #1177005

  I implemented the last post and it is just what I was looking for.

  Great work guys.

  Many Thanks,

  Phil.

  -------------------------------------------------------------------------------------
  A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

  Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

  Tommy Cooper

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  June 7, 2010 at 5:14 am

  #1177014

  skcadavre (6/7/2010)

  ---

  ColdCoffee (6/7/2010)

  ---

  If your request is what Sckadavre is saying, then this might help you! This code doesn use a self-referencing CTE, but assuming your string contains only one Key1: "

  DECLARE @DELIMITER VARCHAR(10)

  SELECT @DELIMITER = '(Key: '

  SELECT

  REPLACE ( (LEFT(STRING , (CHARINDEX(@DELIMITER,STRING)))) ,'(', '') OEM_STRING,

  SUBSTRING( STRING, (CHARINDEX(@DELIMITER,STRING)+ 5 ) , ((DATALENGTH(STRING)-1) - (CHARINDEX(@DELIMITER,STRING)+ 5 )) ) KEY_NUM

  FROM @table

  You fell for the same thing as me, "KEY_NUM" contains a prefix space. 🙂

  Sckadavre, actually there aren't any prefix space mate ; if you could look at the @DELIMITER string, there i have added an extra space, which you haven't; that was the difference!

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  June 7, 2010 at 5:16 am

  #1177015

  2Tall (6/7/2010)

  ---

  I implemented the last post and it is just what I was looking for.

  Great work guys.

  Many Thanks,

  Phil.

  Phil, i have edited my code, please use the latest code. Sorry for the trouble! :pinch:

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  June 7, 2010 at 5:23 am

  #1177017

  ColdCoffee (6/7/2010)

  ---

  skcadavre (6/7/2010)

  ---

  ColdCoffee (6/7/2010)

  ---

  If your request is what Sckadavre is saying, then this might help you! This code doesn use a self-referencing CTE, but assuming your string contains only one Key1: "

  DECLARE @DELIMITER VARCHAR(10)

  SELECT @DELIMITER = '(Key: '

  SELECT

  REPLACE ( (LEFT(STRING , (CHARINDEX(@DELIMITER,STRING)))) ,'(', '') OEM_STRING,

  SUBSTRING( STRING, (CHARINDEX(@DELIMITER,STRING)+ 5 ) , ((DATALENGTH(STRING)-1) - (CHARINDEX(@DELIMITER,STRING)+ 5 )) ) KEY_NUM

  FROM @table

  You fell for the same thing as me, "KEY_NUM" contains a prefix space. 🙂

  Sckadavre, actually there aren't any prefix space mate ; if you could look at the @DELIMITER string, there i have added an extra space, which you haven't; that was the difference!

  Ummm, no? 🙂

  Execute your code and get the results to text.

  DECLARE @table TABLE ( STRING VARCHAR(64) )

  INSERT INTO @table

  SELECT '7487-OEM-00011193-0010(Key: HHYUR-86HZH-99V96-JKL0-88895)'

  UNION ALL SELECT '7487-OEM-00011193-0010(Key: A-86HZH-99V96-JKL0-88895)'

  UNION ALL SELECT '7487-OEM-00011193-0010(Key: BB-86HZH-99V96-JKL0-88895)'

  UNION ALL SELECT '7487-OEM-00011193-0010(Key: CCC-86HZH-99V96-JKL0-88895)'

  UNION ALL SELECT '7487-OEM-00011193-0010(Key: DDDD-86HZH-99V96-JKL0-88895)'

  UNION ALL SELECT '7487-OEM-00011193-0010(Key: EEEEE-86HZH-99V96-JKL0-88895)'

  UNION ALL SELECT '7487-OEM-00011193-0010(Key: FFFFFF-86HZH-99V96-JKL0-88895)'

  DECLARE @DELIMITER VARCHAR(10)

  SELECT @DELIMITER = '(Key: '

  SELECT

  --REPLACE ( (LEFT(STRING , (CHARINDEX(@DELIMITER,STRING)))) ,'(', '') OEM_STRING,

  SUBSTRING( STRING, (CHARINDEX(@DELIMITER,STRING)+ 5 ) , ((DATALENGTH(STRING)-1) - (CHARINDEX(@DELIMITER,STRING)+ 5 )) ) KEY_NUM

  FROM @table

  I've commented out "OEM_STRING". This returns: -

  KEY_NUM

  ----------------------------------------------------------------

  HHYUR-86HZH-99V96-JKL0-8889

  A-86HZH-99V96-JKL0-8889

  BB-86HZH-99V96-JKL0-8889

  CCC-86HZH-99V96-JKL0-8889

  DDDD-86HZH-99V96-JKL0-8889

  EEEEE-86HZH-99V96-JKL0-8889

  FFFFFF-86HZH-99V96-JKL0-8889

  Or your new version, which returns: -

  KEY_NUM

  ----------------------------------------------------------------

  HHYUR-86HZH-99V96-JKL0-88895

  A-86HZH-99V96-JKL0-88895

  BB-86HZH-99V96-JKL0-88895

  CCC-86HZH-99V96-JKL0-88895

  DDDD-86HZH-99V96-JKL0-88895

  EEEEE-86HZH-99V96-JKL0-88895

  FFFFFF-86HZH-99V96-JKL0-88895

  ---

  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/

  ---

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  June 7, 2010 at 5:31 am

  #1177021

  skcadavre (6/7/2010)

  ---

  ColdCoffee (6/7/2010)

  ---

  skcadavre (6/7/2010)

  ---

  ColdCoffee (6/7/2010)

  ---

  If your request is what Sckadavre is saying, then this might help you! This code doesn use a self-referencing CTE, but assuming your string contains only one Key1: "

  DECLARE @DELIMITER VARCHAR(10)

  SELECT @DELIMITER = '(Key: '

  SELECT

  REPLACE ( (LEFT(STRING , (CHARINDEX(@DELIMITER,STRING)))) ,'(', '') OEM_STRING,

  SUBSTRING( STRING, (CHARINDEX(@DELIMITER,STRING)+ 5 ) , ((DATALENGTH(STRING)-1) - (CHARINDEX(@DELIMITER,STRING)+ 5 )) ) KEY_NUM

  FROM @table

  You fell for the same thing as me, "KEY_NUM" contains a prefix space. 🙂

  Sckadavre, actually there aren't any prefix space mate ; if you could look at the @DELIMITER string, there i have added an extra space, which you haven't; that was the difference!

  Ummm, no? 🙂

  Execute your code and get the results to text.

  DECLARE @table TABLE ( STRING VARCHAR(64) )

  INSERT INTO @table

  SELECT '7487-OEM-00011193-0010(Key: HHYUR-86HZH-99V96-JKL0-88895)'

  UNION ALL SELECT '7487-OEM-00011193-0010(Key: A-86HZH-99V96-JKL0-88895)'

  UNION ALL SELECT '7487-OEM-00011193-0010(Key: BB-86HZH-99V96-JKL0-88895)'

  UNION ALL SELECT '7487-OEM-00011193-0010(Key: CCC-86HZH-99V96-JKL0-88895)'

  UNION ALL SELECT '7487-OEM-00011193-0010(Key: DDDD-86HZH-99V96-JKL0-88895)'

  UNION ALL SELECT '7487-OEM-00011193-0010(Key: EEEEE-86HZH-99V96-JKL0-88895)'

  UNION ALL SELECT '7487-OEM-00011193-0010(Key: FFFFFF-86HZH-99V96-JKL0-88895)'

  DECLARE @DELIMITER VARCHAR(10)

  SELECT @DELIMITER = '(Key: '

  SELECT

  --REPLACE ( (LEFT(STRING , (CHARINDEX(@DELIMITER,STRING)))) ,'(', '') OEM_STRING,

  SUBSTRING( STRING, (CHARINDEX(@DELIMITER,STRING)+ 5 ) , ((DATALENGTH(STRING)-1) - (CHARINDEX(@DELIMITER,STRING)+ 5 )) ) KEY_NUM

  FROM @table

  I've commented out "OEM_STRING". This returns: -

  KEY_NUM

  ----------------------------------------------------------------

  HHYUR-86HZH-99V96-JKL0-8889

  A-86HZH-99V96-JKL0-8889

  BB-86HZH-99V96-JKL0-8889

  CCC-86HZH-99V96-JKL0-8889

  DDDD-86HZH-99V96-JKL0-8889

  EEEEE-86HZH-99V96-JKL0-8889

  FFFFFF-86HZH-99V96-JKL0-8889

  Or your new version, which returns: -

  KEY_NUM

  ----------------------------------------------------------------

  HHYUR-86HZH-99V96-JKL0-88895

  A-86HZH-99V96-JKL0-88895

  BB-86HZH-99V96-JKL0-88895

  CCC-86HZH-99V96-JKL0-88895

  DDDD-86HZH-99V96-JKL0-88895

  EEEEE-86HZH-99V96-JKL0-88895

  FFFFFF-86HZH-99V96-JKL0-88895

  You are right, my apologies :crying: ; got my eyes fused i guess.. Thanks for pointing it out.. and Phil, use LTRIM to trim the trailing spaces.. if you are using it for some string comparison, then SQL server itself will take care of it, ; but if u are preseting it to some Drop-down list, then use LTRIM ;

  sorry and thanks again!

• 2Tall

  SSChampion

  Points: 14644

  More actions

  June 7, 2010 at 8:32 am

  #1177099

  Thanks guys, much appreciated. I am not using for any comparison, I am only interested in the key portion of the string.

  Many Thanks,

  Phil.

  PS: Is it an easy mod to remove the ) at the end of the KEY_NUM

  -------------------------------------------------------------------------------------
  A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

  Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

  Tommy Cooper