substring

• harri.reddy

  SSCommitted

  Points: 1957

  More actions

  November 8, 2012 at 8:10 am

  #270745

  i have numbe rlike this

  51079-216-20

  what i need to do it remove - between numbers.also remove last 2 digit

  so

  it should be like 51079216

  not i can have more digit on number also.for example.

  3456-435623-45-34

  i want to remove last two digit ,no matter what the lenght is and also remove - from it

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  November 8, 2012 at 8:16 am

  #1557203

  Like this?

  ;with SampleData (val) as

  (

  select '51079-216-20' union all

  select '3456-435623-45-34'

  )

  select left(replace(val, '-', ''), LEN(replace(val, '-', '')) - 2)

  from SampleData

  Please take note of how I posted sample data to make it easy to work with. You really need to start doing this with your posts.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  November 8, 2012 at 8:49 am

  #1557217

  A simple improvement on Sean solution.

  ;with SampleData (val) as

  (

  select '51079-216-20' union all

  select '3456-435623-45-34'

  )

  SELECT REPLACE( LEFT( val, LEN( val) - 2), '-', '')

  FROM SampleData

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  November 8, 2012 at 9:02 am

  #1557226

  Luis Cazares (11/8/2012)

  ---

  A simple improvement on Sean solution.

  ;with SampleData (val) as

  (

  select '51079-216-20' union all

  select '3456-435623-45-34'

  )

  SELECT REPLACE( LEFT( val, LEN( val) - 2), '-', '')

  FROM SampleData

  Luis that will work with two examples provided but it will not work unless the last 2 characters are numbers. If however, the OP can be 100% certain that the last 2 characters are not "-" then the version Luis posted will perform a little bit better than mine.

  --Sean's version

  ;with SampleData (val) as

  (

  select '51079-216-2-0' union all

  select '3456-435623-45-3-4'

  )

  select left(replace(val, '-', ''), LEN(replace(val, '-', '')) - 2)

  from SampleData

  --Luis's version

  ;with SampleData (val) as

  (

  select '51079-216-2-0' union all

  select '3456-435623-45-3-4'

  )

  SELECT REPLACE( LEFT( val, LEN( val) - 2), '-', '')

  FROM SampleData

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• carlosaamaral

  Old Hand

  Points: 325

  More actions

  November 8, 2012 at 9:22 am

  #1557242

  Hi,

  SELECT NUMBER='51079-216-20' into #number

  UNION ALL

  SELECT NUMBER='3456-435623-45-34'

  SELECT NUMBER=REPLACE(SUBSTRING(NUMBER,1,DATALENGTH(NUMBER)-2),'-','')

  FROM #NUMBER

  [], 🙂

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  November 8, 2012 at 10:13 am

  #1557271

  That's basically the same that I posted, with a simple mistake based on the difference of the behaviour of LEN and DATALENGTH.

  DATALENGTH won't work fine with trailing spaces or unicode values.

  ;with SampleData (val, val2) as

  (

  select '51079-216-20 ', N'51079-216-20' union all

  select '3456-435623-45-34 ', N'3456-435623-45-34'

  )

  SELECT REPLACE(LEFT( val, LEN(val)-2), '-', '') trailing_spaces_LEN,

  REPLACE(LEFT( val2, LEN(val2)-2), '-', '') unicode_LEN,

  REPLACE(SUBSTRING(val,1,DATALENGTH(val)-2),'-','') trailing_spaces_DATALENGTH,

  REPLACE(SUBSTRING(val2,1,DATALENGTH(val2)-2),'-','') unicode_DATALENGTH

  FROM SampleData

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2