-2

I have a col column in the table and want to parse only the Line numbers from this column. Your formula works if the line number has only one letter. But in my case Line number changes up to 4 letters. My column looks like below one, –

enter image description here How to parse from this column? for only line number?

I have used the below formula. but it is throwing me an error for invalid length parameter passed to the substring or left parameter.

= substring(WQ_INFORMATION,charindex('Line:',WQ_INFORMATION)+5, (charindex(',',WQ_INFORMATION,20)-(charindex('Line:',WQ_INFORMATION)+5)))as Line_Number

krishna
  • 1
  • 2
  • 1
    _it is trimming_ What is "it"? Your code? If so, show your code. TRIM will NOT remove anything but leading and trailing spaces. – SMor Aug 18 '21 at 20:33
  • 1
    I can't make any sense of your question, show your sample data and actual desired results in tabular format with a clear explanation of the logic. – Stu Aug 18 '21 at 20:35
  • I have one column that has data 'Prod: UNK, Line=1, LQ=N' in this format. Here, only Line is changing in all rows. other data is the same for Prod and LQ. so I want to create a new column from this column with only Line information. that's why I used the TRIM function. But I think I am wrong. – krishna Aug 18 '21 at 20:47
  • I don't think [trim](https://learn.microsoft.com/en-us/sql/t-sql/functions/trim-transact-sql?view=sql-server-ver15) does what you think it does, and certianly not as presented above. – Stu Aug 18 '21 at 20:50
  • Do you know the right way?@Stu – krishna Aug 18 '21 at 20:54
  • Not without seeing a clear example of your data and desired results as per comment above. – Stu Aug 18 '21 at 20:56
  • May be this one would help you in order to proceed ( this is related to Oracle and I am sure SQL Server must have this functionality). https://stackoverflow.com/questions/51264820/add-a-new-column-and-fill-it-with-a-portion-of-another-column-in-oracle-sql/51265140 – Nadeem Taj Aug 19 '21 at 12:13

2 Answers2

0

trim removes characters from your string, by default it's the whitespace, you can also pass a list of chars to remove other than just whitespace.

more info: https://learn.microsoft.com/en-us/sql/t-sql/functions/trim-transact-sql?view=sql-server-ver15

It seems like you want the data parsed out and put into a table in columns where it can be used. If this is the case here is one way to do that:

create table tester (id int identity(1,1) primary key, col varchar(max),prod varchar(100),Line int, LQ varchar(10))
insert into tester (col) values ('prod: UNK, Line=1, LQ=N')
insert into tester (col) values ('prod: UNK, Line=2, LQ=N')
insert into tester (col) values ('prod: UNK, Line=3, LQ=N')
insert into tester (col) values ('prod: UNB, Line=1, LQ=T')
--parse out prod
update tester set prod = substring(col,charindex('prod: ',col)+6,charindex(',',col)-charindex('prod: ',col)-6) from tester
--parse out Line
update tester set line = substring(col,charindex('Line=',col)+5,charindex(',',col,charindex(',',col)+1)-charindex('Line=',col)-5) from tester
--parse out LQ
update tester set lq = substring(col,charindex('LQ=',col)+3,len(col)+1-charindex('LQ=',col)-3) from tester

select * from tester
drop table tester

Which gives you this:

result

Brian Z
  • 343
  • 1
  • 8
0

You are right. I have a col column in the table and want to parse only the Line numbers from this column. Your formula works if the line number has only one letter. But in my case Line number changes up to 4 letters. My column looks like below one, –

enter image description here

How to parse from this column? for only line number?

@Brian J

I have used the below formula. but it is throwing me an error for invalid length parameter passed to the substring or left parameter.

= substring(WQ_INFORMATION,charindex('Line:',WQ_INFORMATION)+5, (charindex(',',WQ_INFORMATION,20)-(charindex('Line:',WQ_INFORMATION)+5)))as Line_Number

krishna
  • 1
  • 2