Using Split() Function Insert Value in DataBase?

• pheonix_goldensky

  SSC Journeyman

  Points: 80

  More actions

  November 12, 2008 at 12:41 am

  #96722

  Hi,

  I have a Textbox(Multiline) where admin enter value in any format like

  poor,poor,poor,poor

  or in a format like

  poor

  poor

  poor

  poor

  what i need is when value is inserted in Database will done in new row? (I have created Db)

  ID Options

  1 poor

  2 poor

  3 poor

  4 poor

  Any Sample Code

• noviceinsql-938781

  SSC Rookie

  Points: 26

  More actions

  November 12, 2008 at 9:22 pm

  #897884

  If the user enters value one per each line there will be a space between them or else there will be a comma.

  You can try the following.

  DECLARE @data VARCHAR(MAX)

  --two forms of input from multi-line textbox

  SELECT @data = 'poor,poor,poor'

  --select @data = 'poor poor poor'

  --tally table to store each character in a separate row

  SELECT TOP 1000

  IDENTITY(INT,1,1) AS N

  INTO #Tally

  FROM Master.dbo.SysColumns sc1

  ,Master.dbo.SysColumns sc2

  --for consistency replace white spaces in input with commas

  SELECT @data = REPLACE(@data,' ',',')

  --padding

  SET @data = ','+@data + ','

  --parse the string and split it into rows

  SELECT SUBSTRING(@data,N+1,CHARINDEX(',',@data,N+1)-N-1)

  FROM #Tally

  WHERE N < LEN(@data)

  AND SUBSTRING(@data,N,1) = ',' --Notice how we find the comma

  DROP TABLE #Tally

• Jeff Moden

  SSC Guru

  Points: 1004441

  More actions

  November 12, 2008 at 9:56 pm

  #897892

  "Novice" my butt :)... you've been paying attention. 😉

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)